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

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

No, I have not. It is my first contact with Power Query and I am trying understand how it works.