HELP PLEASE! Create columns based on grouped ID

[koenjinosora]

Dear all, 

Nice to meet you. I am a beginner in Power BI and this is my first message. 

I have been searching a way to pivot my table as below, but I could not find the solution yet. 

What I have now is a table with 3 columns like this: 

ID		X		Y
1		34536		57574
1		27192		84732
1		89607		80505
2		61718		98192
2		73829		74628

I would like to group the ID and have new columns for each occurrence of X and Y, like this: 

ID		X_1		Y_1		X_2		Y_2		X_3		Y_3
1		34536		57574		27192		84732		89607		80505
2		61718		98192		73829		74628

Thank you very much in advance for your precious help! 

Best regards, 

Antoine 

[mussaenda · Accepted solution]

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!

[Syndicate_Admin]

Thank you very much both Ashish and Mussaenda!

[Ashish_Mathur]

You are welcome.

[koenjinosora]

Thank you very much both Ashish and Mussaenda! it's working! 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[mussaenda · Accepted solution]

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!

[PhilipTreacy]

@koenjinosora 

Hi Antoine,

Why do you need to transform the table from the correct tabular layout to the 2nd layout with multiple columsn for X/Y co-ords?  What is the result you are trying to achieve?

You're probably better off adding another Index column to uniquely identify each set of co-ords.

Regards

Phil

[koenjinosora]

Hello Phil, thank you very much for your help. The data I'm willing to create is the x y coordinates of different vertices of polygons (ID), coming from GIS shapefile. I need to convert to the second format in order to import it to a java based programming software. 

[koenjinosora]

It seems that it's impossible...