Forum Discussion

PDRTXRA's avatar
PDRTXRA
Helper I
3 years ago
Solved

Transform Table

I think this is very simple but I just can't find a way to do it.

I have a table where each row represents one Family ID and the age of a son of that family.

Lets say: 

FamilyID  |   AgeSon
1   |      10
1   |      12 
2   |      14
3   |     20 
3   |    22
3   |    24

I want to create a table where each row is a family ID with a column for every age. So, using the sample above:

FamilyID | Age1 | Age2 | Age3
1 | 10 | 12 
2 | 14
3 | 20 | 22 | 24

I appreciate your help.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi PDRTXRA 

    You can put the following code to Advanced Editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0UIrVgTKNwEwjENMEzDQGMo0MEEwjBBOoIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FamilyID = _t, AgeSon = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FamilyID", Int64.Type}, {"AgeSon", Int64.Type}}),
    Custom1 = Table.Group(#"Changed Type", {"FamilyID"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"AgeSon", "Index"}, {"AgeSon", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Data", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Data", {{"Index", type text}}, "en-US")[Index]), "Index", "AgeSon"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Age 1"}, {"2", "Age 2"}, {"3", "Age 3"}})
in
    #"Renamed Columns"

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

2 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    3 years ago

    Hi PDRTXRA 
    The first step to achieving your goal is to concatenate your ages :
    you can follow these steps in POWER QUERY :

    1. Change your age data type to text

     

    2. Combine your ages with delimiter:

     

    and paste this m code:

    = Table.Group(#"Changed Type2", {"FamilyID"}, {{"age", each Text.Combine([Age son],","), type nullable text}})

     

    3. Split the result column by comma:

    4. Close and apply.

    Result:

    Link to the sample file 

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi PDRTXRA 

    You can put the following code to Advanced Editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0UIrVgTKNwEwjENMEzDQGMo0MEEwjBBOoIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FamilyID = _t, AgeSon = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FamilyID", Int64.Type}, {"AgeSon", Int64.Type}}),
    Custom1 = Table.Group(#"Changed Type", {"FamilyID"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"AgeSon", "Index"}, {"AgeSon", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Data", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Data", {{"Index", type text}}, "en-US")[Index]), "Index", "AgeSon"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Age 1"}, {"2", "Age 2"}, {"3", "Age 3"}})
in
    #"Renamed Columns"

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.