Transform Table

[PDRTXRA]

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.

[v-xinruzhu-msft · Accepted solution]

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.

[Ritaf1983]

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