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 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")

 

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