ExpandListColumn for all columns

[Anonymous]

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

[Anonymous]

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

[Vera_33]

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]

Hi @Vera_33 

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.

[Vera_33 · Accepted solution]

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]

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]

Hi @Anonymous 

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