cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hashtag_pete
Resolver I
Resolver I

transform rows to columns

Hello folks, 

I have a table from Business Central which I need to transform from this:

Dimension Set IDDimension_CodeDimension Value Code
10OperationDE052-01
10Service5201
17BA120
17OperationDE053-01
17Service5301

to this:

Dimension Set IDDimension_Code1Dimension_Code2Dimension_Code3Dimension Value Code1Dimension Value Code2Dimension Value Code3
10OperationServicenullDE052-015201null
17BAOperationService120DE053-015301

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

1 ACCEPTED SOLUTION
Vera_33
Super User
Super User

Hi @hashtag_pete 

 

Here is one way, combine the values then split

Vera_33_0-1641374885369.png

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"

 

View solution in original post

2 REPLIES 2
hashtag_pete
Resolver I
Resolver I

Thank you, @Vera_33 , that's brilliant!

Vera_33
Super User
Super User

Hi @hashtag_pete 

 

Here is one way, combine the values then split

Vera_33_0-1641374885369.png

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"

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors