Forum Discussion

Anonymous

Not applicable

2 years ago

Transform multiple columns based on values in another column

Hello, I am trying to group, split, or somehow transform multiple columns (Years 2020-2023) based on values in another column (Source) for visualization in Power BI. The original data comes from ...

"Need Help"

"Power Query"

Anonymous

Not applicable

2 years ago

** Update **

Raw Excel data I have currently:

aaa	bbb	Customer	ddd	eee	fff	ggg	Source	2020	2021	2022	2023
a1	b1	Customer 1	d1	e1	f1	g1	Source A
a1	b1	Customer 1	d1	e1	f1	g1	Source B
a1	b1	Customer 1	d1	e1	f1	g1	Source C
a2	b2	Customer 1	d2	e2	f2	g2	Source A
a2	b2	Customer 1	d2	e2	f2	g2	Source B
a2	b2	Customer 1	d2	e2	f2	g2	Source C
a3	b3	Customer 1	d3	e3	f3	g3	Source A
a3	b3	Customer 1	d3	e3	f3	g3	Source B
a3	b3	Customer 1	d3	e3	f3	g3	Source C
a4	b4	Customer 2	d4	e4	f4	g4	Source A
a4	b4	Customer 2	d4	e4	f4	g4	Source B
a4	b4	Customer 2	d4	e4	f4	g4	Source C
a5	b5	Customer 2	d5	e5	f5	g5	Source A
a5	b5	Customer 2	d5	e5	f5	g5	Source B
a5	b5	Customer 2	d5	e5	f5	g5	Source C
a6	b6	Customer 2	d6	e6	f6	g6	Source A
a6	b6	Customer 2	d6	e6	f6	g6	Source B
a6	b6	Customer 2	d6	e6	f6	g6	Source C
a7	b7	Customer 2	d7	e7	f7	g7	Source A
a7	b7	Customer 2	d7	e7	f7	g7	Source B
a7	b7	Customer 2	d7	e7	f7	g7	Source C
a8	b8	Customer 3	d8	e8	f8	g8	Source A
a8	b8	Customer 3	d8	e8	f8	g8	Source B
a8	b8	Customer 3	d8	e8	f8	g8	Source C

And this is the end result I am looking for:

aaa

bbb

Customer

ddd

eee

fff

ggg

2020 A

2020 B

2020 C

2021 A

2021 B

2021 C

2022 A

2022 B

2022 C

2023 A

2023 B

2023 C

Customer 1

Customer 2

Customer 3

Thank you!

adudani

Memorable Member

2 years ago

hi Anonymous ,

Create a blank query , copy and paste the below code into the advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdPNCoJQEIbhWxHXbvJ/W15CS3FROXNWIVjef72rpDMcmAjkQ2fxwLtwHPPLIS/yKzNsj+dylzXjY2aEUSYw52Vbb5Id36+fZyp+MU5/MIbYKDHKyOAijDKhTLa4DaPFbRgtFUYVGVyEUSZUyRa3YbS4DaOlxqj3Bu0zF2GUCXWyxW0YLW7DaGkwmsjgIowyoUm2uA2jxW0YLS1GGxlchFEmtMkWt2G0uA2jpcPoIoOLMMqELtniNowWt2G09Bj93uD/mrkIo0zoky1uw2hxG18t0ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [aaa = _t, bbb = _t, Customer = _t, ddd = _t, eee = _t, fff = _t, ggg = _t, Source = _t, #"2020" = _t, #"2021" = _t, #"2022" = _t, #"2023" = _t]),
    Columns_not_to_Unpivot = Table.AddIndexColumn ( Table.FromList ( List.FirstN ( Table.ColumnNames( Source) , 8))  , "Index" ,1,1),
    #"Removed Other Columns" = Table.SelectColumns(Columns_not_to_Unpivot,{"Column1"}),
    Columns_not_to_Unpivot_List = #"Removed Other Columns"[Column1],
    Refer_to_Source = Source,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Refer_to_Source, Columns_not_to_Unpivot_List, "Year", "Value"),
    #"Merged Source and Year" = Table.CombineColumns(#"Unpivoted Other Columns",{"Source", "Year"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Source and Year"

Output:

The last step would be the structure the Merge and Value column as needed etc.

However, I recommend you load it as is in Power query and use a matrix visual instead for the same.

If the goal is to create a pivot table using Power query or similar , refer to Subtotal and Column Total in Power Query - YouTube

Anonymous

Not applicable

2 years ago

Thank you! I would need to use a table instead of matrix for the visualization.

Based on the output you provided, how can I merge the rows in the same order (ex: the a1s, the a2s) into one row, with the Merged column expanded as multiple columns?

This is what I'm referring to:

aaa

bbb

Customer

ddd

eee

fff

A2020

A2021

A2022

A2023

B2020

B2021

B2022

B2023

...

Customer 1

adudani

Memorable Member

2 years ago

paste the following codes into 2 different blank queries

query1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdPNCoJQEIbhWxHXbvJ/W15CS3FROXNWIVjef72rpDMcmAjkQ2fxwLtwHPPLIS/yKzNsj+dylzXjY2aEUSYw52Vbb5Id36+fZyp+MU5/MIbYKDHKyOAijDKhTLa4DaPFbRgtFUYVGVyEUSZUyRa3YbS4DaOlxqj3Bu0zF2GUCXWyxW0YLW7DaGkwmsjgIowyoUm2uA2jxW0YLS1GGxlchFEmtMkWt2G0uA2jpcPoIoOLMMqELtniNowWt2G09Bj93uD/mrkIo0zoky1uw2hxG18t0ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [aaa = _t, bbb = _t, Customer = _t, ddd = _t, eee = _t, fff = _t, ggg = _t, Source = _t, #"2020" = _t, #"2021" = _t, #"2022" = _t, #"2023" = _t]),
    Columns_not_to_Unpivot = Table.AddIndexColumn ( Table.FromList ( List.FirstN ( Table.ColumnNames( Source) , 8))  , "Index" ,1,1),
    #"Removed Other Columns" = Table.SelectColumns(Columns_not_to_Unpivot,{"Column1"}),
    Columns_not_to_Unpivot_List = #"Removed Other Columns"[Column1],
    Refer_to_Source = Source,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Refer_to_Source, Columns_not_to_Unpivot_List, "Year", "Value"),
    #"Merged Source and Year" = Table.CombineColumns(#"Unpivoted Other Columns",{"Source", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Source and Year","Source","",Replacer.ReplaceText,{"Merged"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"aaa", "bbb", "Customer", "ddd", "eee", "fff", "ggg"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{" A 2020", type text}, {" A 2021", type text}, {" A 2022", type text}, {" A 2023", type text}, {" B 2020", type text}, {" B 2021", type text}, {" B 2022", type text}, {" B 2023", type text}, {" C 2020", type text}, {" C 2021", type text}, {" C 2022", type text}, {" C 2023", type text}, {" A 2020_1", type text}, {" A 2021_2", type text}, {" A 2022_3", type text}, {" A 2023_4", type text}, {" B 2020_5", type text}, {" B 2021_6", type text}, {" B 2022_7", type text}, {" B 2023_8", type text}, {" C 2020_9", type text}, {" C 2021_10", type text}, {" C 2022_11", type text}, {" C 2023_12", type text}, {" A 2020_13", type text}, {" A 2021_14", type text}, {" A 2022_15", type text}, {" A 2023_16", type text}, {" B 2020_17", type text}, {" B 2021_18", type text}, {" B 2022_19", type text}, {" B 2023_20", type text}, {" C 2020_21", type text}, {" C 2021_22", type text}, {" C 2022_23", type text}, {" C 2023_24", type text}, {" A 2020_25", type text}, {" A 2021_26", type text}, {" A 2022_27", type text}, {" A 2023_28", type text}, {" B 2020_29", type text}, {" B 2021_30", type text}, {" B 2022_31", type text}, {" B 2023_32", type text}, {" C 2020_33", type text}, {" C 2021_34", type text}, {" C 2022_35", type text}, {" C 2023_36", type text}, {" A 2020_37", type text}, {" A 2021_38", type text}, {" A 2022_39", type text}, {" A 2023_40", type text}, {" B 2020_41", type text}, {" B 2021_42", type text}, {" B 2022_43", type text}, {" B 2023_44", type text}, {" C 2020_45", type text}, {" C 2021_46", type text}, {" C 2022_47", type text}, {" C 2023_48", type text}, {" A 2020_49", type text}, {" A 2021_50", type text}, {" A 2022_51", type text}, {" A 2023_52", type text}, {" B 2020_53", type text}, {" B 2021_54", type text}, {" B 2022_55", type text}, {" B 2023_56", type text}, {" C 2020_57", type text}, {" C 2021_58", type text}, {" C 2022_59", type text}, {" C 2023_60", type text}, {" A 2020_61", type text}, {" A 2021_62", type text}, {" A 2022_63", type text}, {" A 2023_64", type text}, {" B 2020_65", type text}, {" B 2021_66", type text}, {" B 2022_67", type text}, {" B 2023_68", type text}, {" C 2020_69", type text}, {" C 2021_70", type text}, {" C 2022_71", type text}, {" C 2023_72", type text}, {" A 2020_73", type text}, {" A 2021_74", type text}, {" A 2022_75", type text}, {" A 2023_76", type text}, {" B 2020_77", type text}, {" B 2021_78", type text}, {" B 2022_79", type text}, {" B 2023_80", type text}, {" C 2020_81", type text}, {" C 2021_82", type text}, {" C 2022_83", type text}, {" C 2023_84", type text}})
in
    #"Changed Type"

query2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdPNCoJQEIbhWxHXbvJ/W15CS3FROXNWIVjef72rpDMcmAjkQ2fxwLtwHPPLIS/yKzNsj+dylzXjY2aEUSYw52Vbb5Id36+fZyp+MU5/MIbYKDHKyOAijDKhTLa4DaPFbRgtFUYVGVyEUSZUyRa3YbS4DaOlxqj3Bu0zF2GUCXWyxW0YLW7DaGkwmsjgIowyoUm2uA2jxW0YLS1GGxlchFEmtMkWt2G0uA2jpcPoIoOLMMqELtniNowWt2G09Bj93uD/mrkIo0zoky1uw2hxG18t0ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [aaa = _t, bbb = _t, Customer = _t, ddd = _t, eee = _t, fff = _t, ggg = _t, Source = _t, #"2020" = _t, #"2021" = _t, #"2022" = _t, #"2023" = _t]),
    Columns_not_to_Unpivot = Table.AddIndexColumn ( Table.FromList ( List.FirstN ( Table.ColumnNames( Source) , 8))  , "Index" ,1,1),
    #"Removed Other Columns" = Table.SelectColumns(Columns_not_to_Unpivot,{"Column1"}),
    Columns_not_to_Unpivot_List = #"Removed Other Columns"[Column1],
    Refer_to_Source = Source,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Refer_to_Source, Columns_not_to_Unpivot_List, "Year", "Value"),
    #"Merged Source and Year" = Table.CombineColumns(#"Unpivoted Other Columns",{"Source", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Source and Year","Source","",Replacer.ReplaceText,{"Merged"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Replaced Value",{"aaa", "bbb", "Customer", "ddd", "eee", "fff", "ggg"}),
    #"Appended Query" = Table.Combine({#"Removed Other Columns1", Query1})
in
    #"Appended Query"

Anonymous
Not applicable
2 years ago
Sorry for the back and forth... I received an expression error saying "Expression.Error: A cyclic reference was encountered during evaluation."

Forum Discussion

Transform multiple columns based on values in another column

Related content

Transform columns / type

transform multiple columns into 2 columns (List.Zip, (un)pivot)

Table Transformation

Transforming multiple columns

Transform Column / Type