Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

So at the top of your list column(s), on the right, you can click the arrows, and choose to "Extract Values", which will expand the whole list onto that same row, as oposed to creating new rows, which will of course lead to---extra rows.

 

--Nate