Forum Discussion
Solved
transform rows to columns
Hello folks,
I have a table from Business Central which I need to transform from this:
| Dimension Set ID | Dimension_Code | Dimension Value Code |
| 10 | Operation | DE052-01 |
| 10 | Service | 5201 |
| 17 | BA | 120 |
| 17 | Operation | DE053-01 |
| 17 | Service | 5301 |
to this:
| Dimension Set ID | Dimension_Code1 | Dimension_Code2 | Dimension_Code3 | Dimension Value Code1 | Dimension Value Code2 | Dimension Value Code3 |
| 10 | Operation | Service | null | DE052-01 | 5201 | null |
| 17 | BA | Operation | Service | 120 | DE053-01 | 5301 |
Can anyone help on this?
Reason is, that I have in BC my fixed assets table, where only the Dimension Set ID is given, but I want to have the other information in this row as well. When I do the join of those two tables, Power Query will double or triple each line for the information of the dimension set.
Thanks
hashtag_pete
Hi hashtag_pete
Here is one way, combine the values then split
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lHyL0gtSizJzM8Dsl1cDUyNdA0MlWJ1oLLBqUVlmcmpQJapEUzcHMhzcgQShkYGCBF0c4x1kdUjmWMMEo8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dimension Set ID" = _t, Dimension_Code = _t, #"Dimension Value Code" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dimension Set ID", Int64.Type}, {"Dimension_Code", type text}, {"Dimension Value Code", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Dimension Set ID"}, {{"Dimension_Code", each Text.Combine([Dimension_Code],";")},{"Dimension Value Code",each Text.Combine([Dimension Value Code],";")}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Dimension_Code", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Dimension Value Code", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)) in #"Split Column by Delimiter1"
2 Replies
Hi hashtag_pete
Here is one way, combine the values then split
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lHyL0gtSizJzM8Dsl1cDUyNdA0MlWJ1oLLBqUVlmcmpQJapEUzcHMhzcgQShkYGCBF0c4x1kdUjmWMMEo8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dimension Set ID" = _t, Dimension_Code = _t, #"Dimension Value Code" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dimension Set ID", Int64.Type}, {"Dimension_Code", type text}, {"Dimension Value Code", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Dimension Set ID"}, {{"Dimension_Code", each Text.Combine([Dimension_Code],";")},{"Dimension Value Code",each Text.Combine([Dimension Value Code],";")}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Dimension_Code", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Dimension Value Code", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)) in #"Split Column by Delimiter1"Thank you, Anonymous , that's brilliant!
