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

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_7

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

     

    Yes, It is what I want to do. The reason for this is, I just want to prepare (x,y)chart with all serial numbers, so I try to estimate X and Y values for every serial number.

    • Anonymous's avatar
      Anonymous
      Not applicable
      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