HELP PLEASE! Create columns based on grouped ID

Hi koenjinosora,

What you need is not impossible.

Please try the code below and paste to a Blank Query Advance Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcu5DcAwDEPRXVS7sE5Ksxjef41YDpLqAR/kWsQ0SM01jg6H0R5vFXDJMQ0qf82KiXb69Ft7EwzOY2V/vgpNqdZCkvZ+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, X = _t, Y = _t]),
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"ALL", each _, type table [ID=nullable number, X=nullable number, Y=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([ALL], "IndexByID", 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"X", "Y", "IndexByID"}, {"X", "Y", "IndexByID"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Expanded Custom", {"X", "Y"}, "Attribute", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each [Attribute] & "_" & Number.ToText([IndexByID])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"ALL", "IndexByID", "Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"X_1", Int64.Type}, {"Y_1", Int64.Type}, {"X_2", Int64.Type}, {"Y_2", Int64.Type}, {"X_3", Int64.Type}, {"Y_3", Int64.Type}})
in
    #"Changed Type"

But..

What is the maximum count of your X/Y per ID?

Please take note that it is better not to do like this if you have more X/Y because you are creating multiple columns per ID.

Hope this helps!

Hi,

You may download my PBI file from here.

Hope this helps.