My question is similar to this post (How to pivot a table in excel from long to wide) except that I don’t know how many columns to be add. In other words, one unique person can have more than one rows but I don’t know how many rows could there be (this is dynamic that cannot be predetermined). It would be very helpful if someone could show me how to set up the DAX code or get the Power Query set up.
Here is an example original tall table:
|ParentID|Parent Age|Child ID |Child Name|Child Rank|Child Hobby|
|:-------|:-----|:---------|:-------|:----------|:------|
|1|30|10|X|1|foot ball|
|1|30|11|Y|2|paint|
|1|30|12|Z|3|piano|
|2|23|13|A|3|foot ball|
|2|23|14|B|4|violin|
|3|44|15|D|2|foot ball|
|4|45|16|E|1|basketabll|
The result I want to get:
|ParentID|Parent Age|ChildID.1|ChildName.1|ChildRank.1|ChildHobby.1|ChildID.2|ChildName.|ChildRank.2|ChildHobby.2|ChildID.3|ChildName.3|ChildRank.3|ChildHobby.3|
|:------|:------|:--------|:--------|:--------|:---------|:--------|:--------|:---------|:--------|:--------|---------|:--------|:--------|
|1|30|10|X|1|foot ball|11|Y|2|paint|12|Z|3|piano|
|2|23|13|A|4|foot ball|14|B|2|violin|||||
|3|44|15|D|2|foot ball||||||||||
|4|45|16|E|1|basketball||||||||||
you can try this in PQ
- merge parent columns
- merge child columns
- group by merge column and combine merged.1 column
- Then you split the columns
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY/NCsMgEIRfpXj20E1N7yntOySRHFZoQCpaiOT5syNCYw8zrvMN/lirSGl1u4oRbMQgWlPKF8chqEWfO5gmUSf6so/5jyOfsQP3HFPhSDtEBBsqb+/4dYzYQ4R19yn4WApgBhn1Ys/6iPYQYANMd7FX/Yzj7fPO7FBaDg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ParentID = _t, #"Parent Age" = _t, #"Child ID " = _t, #"Child Name" = _t, #"Child Rank" = _t, #"Child Hobby" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ParentID", Int64.Type}, {"Parent Age", Int64.Type}, {"Child ID ", Int64.Type}, {"Child Name", type text}, {"Child Rank", Int64.Type}, {"Child Hobby", type text}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"ParentID", type text}, {"Parent Age", type text}}, "zh-CN"),{"ParentID", "Parent Age"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Child ID ", type text}, {"Child Rank", type text}}, "zh-CN"),{"Child ID ", "Child Name", "Child Rank", "Child Hobby"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged.1"),
#"Grouped Rows" = Table.Group(#"Merged Columns1", {"Merged"}, {{"column", each Text.Combine([Merged.1],","), type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Merged", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", Int64.Type}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "column", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"column.1", "column.2", "column.3", "column.4", "column.5", "column.6", "column.7", "column.8", "column.9", "column.10"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"column.1", Int64.Type}, {"column.2", type text}, {"column.3", Int64.Type}, {"column.4", type text}, {"column.5", type text}, {"column.6", Int64.Type}, {"column.7", type text}, {"column.8", type text}, {"column.9", Int64.Type}, {"column.10", type text}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "column.4", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"column.4.1", "column.4.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"column.4.1", type text}, {"column.4.2", Int64.Type}}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "column.7", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"column.7.1", "column.7.2"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"column.7.1", type text}, {"column.7.2", Int64.Type}})
in
#"Changed Type4"