Forum Discussion
ExpandListColumn for all columns
Hi,
I couldn't solve how to use ExpandListColumn formule for all columns, which values has list format.
Everything is easy for one column, but problem starts when I want do the same for all data from .json file:
let
Source = Json.Document(File.Contents("C:\Users\daniel.duda\Desktop\MKC2\json\history_1624536103630.json")),
Step_1 = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Step_2 = Table.ExpandRecordColumn(Step_1, "Column1", {"id", "points"}, {"Column1.id", "Column1.points"}),
Step_3 = Table.SelectRows(Step_2, each ([Column1.id] = "60cc8d5023ec6a04764e1648" or [Column1.id] = "60cc8d5823ec6a04764e1649" or [Column1.id] = "60cc8d6123ec6a04764e164a" or [Column1.id] = "60cc8d6923ec6a04764e164b" or [Column1.id] = "60cc8d7123ec6a04764e164c" or [Column1.id] = "60cc8d7923ec6a04764e164d" or [Column1.id] = "60cc8d8123ec6a04764e164e" or [Column1.id] = "60cc8d8a23ec6a04764e164f" or [Column1.id] = "60cc8d9223ec6a04764e1650" or [Column1.id] = "60cc8d9a23ec6a04764e1651" or [Column1.id] = "60cc8da223ec6a04764e1652")),
Step_4 = Table.Transpose(Step_3),
Step_5 = Table.PromoteHeaders(Step_4, [PromoteAllScalars=true]),
Step_6 = Table.TransformColumnTypes(Step_5,{{"60cc8da223ec6a04764e1652", type any}, {"60cc8d9a23ec6a04764e1651", type any}, {"60cc8d9223ec6a04764e1650", type any}, {"60cc8d8a23ec6a04764e164f", type any}, {"60cc8d8123ec6a04764e164e", type any}, {"60cc8d7923ec6a04764e164d", type any}, {"60cc8d7123ec6a04764e164c", type any}, {"60cc8d6923ec6a04764e164b", type any}, {"60cc8d6123ec6a04764e164a", type any}, {"60cc8d5823ec6a04764e1649", type any}, {"60cc8d5023ec6a04764e1648", type any}}),
Step_7 = Table.ExpandListColumn(Step_6, "60cc8da223ec6a04764e1652"),
Step_8 = Table.ExpandRecordColumn(Step_7, "60cc8da223ec6a04764e1652", {"x", "y"}, {"60cc8da223ec6a04764e1652.x", "60cc8da223ec6a04764e1652.y"})
in
Step_8
Problem starts with "Step_7".
Down below you have Screenshot from table after Step_7 for one column:
I treid to use that code, but it doesn't works...:
<PreviousStep> = some table,
Add_Zipped = Table.AddColumn(<PreviousStep>, "Zipped", each List.Zip({[Column1], [Column2]}), type list),
Expand_Zipped = Table.ExpandListColumn(Add_Zipped, "Zipped")
Hi Anonymous
Probably you need this output, let me know if you want something different
let Source = Json.Document(File.Contents("C:\Users\daniel.duda\Desktop\MKC2\json\history_1624536103630.json")), Step_1 = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Step_2 = Table.ExpandRecordColumn(Step_1, "Column1", {"id", "points"}, {"id", "points"}), idList = {"60cc8d5023ec6a04764e1648" , "60cc8d5823ec6a04764e1649" , "60cc8d6123ec6a04764e164a" ,"60cc8d6923ec6a04764e164b" , "60cc8d7123ec6a04764e164c", "60cc8d7923ec6a04764e164d" ,"60cc8d8123ec6a04764e164e" , "60cc8d8a23ec6a04764e164f" , "60cc8d9223ec6a04764e1650" , "60cc8d9a23ec6a04764e1651" ,"60cc8da223ec6a04764e1652"}, Step_3 = Table.SelectRows(Step_2, each List.Contains(idList,[id])), Step_4 = Table.ExpandRecordColumn( Table.ExpandListColumn(Step_3, "points"),"points", {"x","y"}) in Step_4
6 Replies
Hi Anonymous
I am not sure why you need all the id.x, id.y columns, but is it what you want? I am running into another direction
let Source = Json.Document(File.Contents("C:\Users\daniel.duda\Desktop\MKC2\json\history_1624536103630.json")), Step_1 = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Step_2 = Table.ExpandRecordColumn(Step_1, "Column1", {"id", "points"}, {"id", "points"}), idList = {"60cc8d5023ec6a04764e1648" , "60cc8d5823ec6a04764e1649" , "60cc8d6123ec6a04764e164a" ,"60cc8d6923ec6a04764e164b" , "60cc8d7123ec6a04764e164c", "60cc8d7923ec6a04764e164d" ,"60cc8d8123ec6a04764e164e" , "60cc8d8a23ec6a04764e164f" , "60cc8d9223ec6a04764e1650" , "60cc8d9a23ec6a04764e1651" ,"60cc8da223ec6a04764e1652"}, Step_3 = Table.SelectRows(Step_2, each List.Contains(idList,[id])), Step_4 = Table.TransformColumns(Step_3, {{"points", each Table.AddIndexColumn( Table.FromRecords(_),"Index",0,1)}}), Step_5 = Table.ReorderColumns(Table.ExpandTableColumn( Step_4,"points",{"x","y","Index"}),{"Index", "id", "x", "y"}), xTable = Table.TransformColumnNames( Table.Pivot(Table.SelectColumns( Step_5,{"Index", "id", "x"}),idList, "id", "x"), each _&".x"), yTable = Table.TransformColumnNames( Table.Pivot(Table.SelectColumns( Step_5,{"Index", "id", "y"}),idList, "id", "y"), each _&".y"), Step_6 = Table.Join(xTable, "Index.x", yTable,"Index.y"), Step_7 = Table.ReorderColumns(Step_6, List.Combine( List.Zip({List.Transform(idList, each _&".x"), List.Transform(idList, each _&".y")}))) in Step_7Hi Anonymous
Probably you need this output, let me know if you want something different
let Source = Json.Document(File.Contents("C:\Users\daniel.duda\Desktop\MKC2\json\history_1624536103630.json")), Step_1 = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Step_2 = Table.ExpandRecordColumn(Step_1, "Column1", {"id", "points"}, {"id", "points"}), idList = {"60cc8d5023ec6a04764e1648" , "60cc8d5823ec6a04764e1649" , "60cc8d6123ec6a04764e164a" ,"60cc8d6923ec6a04764e164b" , "60cc8d7123ec6a04764e164c", "60cc8d7923ec6a04764e164d" ,"60cc8d8123ec6a04764e164e" , "60cc8d8a23ec6a04764e164f" , "60cc8d9223ec6a04764e1650" , "60cc8d9a23ec6a04764e1651" ,"60cc8da223ec6a04764e1652"}, Step_3 = Table.SelectRows(Step_2, each List.Contains(idList,[id])), Step_4 = Table.ExpandRecordColumn( Table.ExpandListColumn(Step_3, "points"),"points", {"x","y"}) in Step_4
You could combine the list values for each row, then you'll have just one list column to expand. Then you can split the columns back to what they are supposed to be (I hope you aren't keepers by those column names!).
Try typing this into the formula bar; using List.Combine instead of List.Zip:
Table.AddColumn(NameOfPriorStep, "Combined", each List.Combine({[Name of List Column1], [Name of List Column 2], [Name of List Column 3]})
But have you tried extracting the list values, instead of expanding to new rows?
--Nate