I have JSON in format like this:
{ "data": [ { "date": "2021-01-01", "data": 10 }, { "date": "2021-01-02", "data": 20 }, { "date": "2021-01-03", "data": 30 } ]
}I'm able to write this code which does exaclty what I want:
let Source = Json.Document(...), data = Source[data], #"ToTable" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Block 1 Start Column1 = #"ToTable"{0}[Column1], //Gets Record from 1st "data" Array item #"ToTable1" = Record.ToTable(Column1), //Create table with two columns (key, value) #"Transpose1" = Table.Transpose(#"ToTable1"), //Transpose the table #"Headers1" = Table.PromoteHeaders(#"Transpose1", [PromoteAllScalars=true]), //Get headers from the 1st row (key names) #"ChangeType1" = Table.TransformColumnTypes(#"Headers1",{{"date", type date}, {"value", Int64.Type}, type any}}), //Change Type
// Block 1 End
// Block 2 Start Column2 = #"ToTable"{1}[Column1], //Gets Record from 2nd "data" Array item #"ToTable2" = Record.ToTable(Column2), #"Transpose2" = Table.Transpose(#"ToTable2"), #"Headers2" = Table.PromoteHeaders(#"Transpose2", [PromoteAllScalars=true]), #"ChangeType2" = Table.TransformColumnTypes(#"Headers2",{{"date", type date}, {"value", Int64.Type}, type any}}),
// Block 2 End
// Block 3 Start Column3 = #"ToTable"{2}[Column1], //Gets Record from 3rd "data" Array item #"ToTable3" = Record.ToTable(Column3), #"Transpose3" = Table.Transpose(#"ToTable3"), #"Headers3" = Table.PromoteHeaders(#"Transpose3", [PromoteAllScalars=true]), #"ChangeType3" = Table.TransformColumnTypes(#"Headers3",{{"date", type date}, {"value", Int64.Type}, type any}}),
// Block 3 End TheTable = Table.Combine({ChangeType1, ChangeType2, ChangeType3}) //Combines tables together
in TheTableBUT
This all is OK for 3 items in "data" Array, not when there are hundreds of them.
Is there some trick how to repeat Code block for each row in data[Column1], get a List of Tables (ChangeTypeX) to use in Table.Combine?
2 Answers
It appears you want to have a result with two columns date and data. If that is the case, all you need to do is expand the table that results from Step 3.
Merely click the double headed arrow at the top of the column
M Code
let Source = Json.Document(File.Contents("C:\Users\ron\Desktop\new 3.json")), data = Source[data], #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"date", "data"}, {"date", "data"})
in #"Expanded Column1"Result
You should also set the data types after you have expanded the columns
Lets start from beginning
let Source = Json.Document(...), data = Source[data], #"ToTable1" = Record.ToTable(Column1)Now we have List of Records stored in #"ToTable1"[Column1]. (Because table Columns are Lists.)
So we can use List.Transform function to manipulate with each Item of this List.
Syntax is
List.Transform(list as list, transform as function) as listSo we need some transform function which works with each Item of the List separately. Let modify a Code Block from the Original question.
MyFunc = (ColumnRow) =>
let #"ToTableX" = Record.ToTable(ColumnRow), #"TransposeX" = Table.Transpose(#"ToTableX"), #"HeadersX" = Table.PromoteHeaders(#"TransposeX", [PromoteAllScalars=true]), #"ChangeTypeX" = Table.TransformColumnTypes(#"HeadersX",{{"date", type date}, {"value", Int64.Type}, type any}})
in
#"ChangeTypeX"So whole List.Tranform function code looks like
List.Transform(#"ToTable1"[Column1], MyFunc)This returns a List of Tables which could be used in Table.Combine function.
MyResultTable = Table.Combine(List.Transform(#"ToTable1"[Column1], MyFunc))Put it all together
let Source = Json.Document(...), data = Source[data], #"ToTable1" = Record.ToTable(Column1)
// End of original code used here MyList = #"ToTable1"[Column1], //For better readability MyFunc = (ColumnRow) => let #"ToTableX" = Record.ToTable(ColumnRow), #"TransposeX" = Table.Transpose(#"ToTableX"), #"HeadersX" = Table.PromoteHeaders(#"TransposeX", [PromoteAllScalars=true]), #"ChangeTypeX" = Table.TransformColumnTypes(#"HeadersX",{{"date", type date}, {"value", Int64.Type}, type any}}) in #"ChangeTypeX", MyResultTable = Table.Combine(List.Transform(MyList, MyFunc)) in MyResultTable 
