Forum Discussion

koenjinosora's avatar
koenjinosora
Regular Visitor
5 years ago
Solved

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 

Need Help
  • mussaenda's avatar
    mussaenda
    5 years ago

    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!

     

     

8 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    5 years ago

    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's avatar
    PhilipTreacy
    Super User
    5 years ago

    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's avatar
      koenjinosora
      Regular Visitor
      5 years ago

      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.