Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
koenjinosora
Regular Visitor

HELP PLEASE! Create columns based on grouped ID

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 

2 ACCEPTED SOLUTIONS
mussaenda
Super User
Super User

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"

 

 

Untitled.png

 

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!

 

 

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Syndicate_Admin
Administrator
Administrator

Thank you very much both Ashish and Mussaenda!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
koenjinosora
Regular Visitor

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

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mussaenda
Super User
Super User

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"

 

 

Untitled.png

 

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
Super User
Super User

@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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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
Regular Visitor

It seems that it's impossible...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.