Transform multiple columns based on values in another column

Question

Hello,&nbsp;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 Excel. How can I do the following?&nbsp;This is the raw data I have right now, where I have multiple orders per customer, and each order being estimated based on Source A, B, and C. Specifically, I have 3 orders from Customer 1, and rows 2-4 are about the 1st order. In these 3 rows, values are the same for columns aaa, bbb, ddd, eee, fff, and ggg, so it is essentially a repetition. I have numbers in columns 2020-2023, but I don't want to view Source horizontally. Instead, I would like to incorporate Source into the Year columns.&nbsp;&nbsp;&nbsp;This is the end product I want. Each order is only 1 row, and Source is incorporated into the Year columns such that I have 2020A, 2020B, 2020C, 2021A... and so on.&nbsp;Really appreciate your help. Thanks!

anonymous · Answer

Thank you for the information! Have provided the sample data in my update. Thank you!

foodd · Answer

Hello&nbsp;Anonymous&nbsp;, and thank you for sharing a question with the Community.&nbsp;&nbsp;This reply is informational. Please follow the decorum of the Community Forum when asking a question. Please share your work-in-progress Power BI Desktop file (with sensitive information removed) and any source files in Excel format that fully address your issue or question in a usable format (not as a screenshot). You can upload these files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then provide the file's URL.https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216Please show the expected outcome based on the sample data you provided.https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.
&nbsp;
If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.&nbsp; Proud to be a Super User!

adudani · Answer

hi&nbsp;Anonymous&nbsp;,
&nbsp;
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"
&nbsp;
Output:&nbsp;
&nbsp;

&nbsp;
&nbsp;
The last step would be the structure the Merge and Value column as needed etc.
&nbsp;
However, I recommend you load it as is in Power query and use a matrix visual instead for the same.
&nbsp;
If the goal is to create a pivot table using Power query or similar , refer to&nbsp;Subtotal and Column Total in Power Query - YouTube&nbsp;

adudani · Answer

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"
&nbsp;
&nbsp;
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"
&nbsp;

anonymous · Answer

** Update **&nbsp;Raw Excel data I have currently:aaabbbCustomerdddeeefffgggSource2020202120222023a1b1Customer 1d1e1f1g1Source A&nbsp;&nbsp;&nbsp;&nbsp;a1b1Customer 1d1e1f1g1Source B&nbsp;&nbsp;&nbsp;&nbsp;a1b1Customer 1d1e1f1g1Source C&nbsp;&nbsp;&nbsp;&nbsp;a2b2Customer 1d2e2f2g2Source A&nbsp;&nbsp;&nbsp;&nbsp;a2b2Customer 1d2e2f2g2Source B&nbsp;&nbsp;&nbsp;&nbsp;a2b2Customer 1d2e2f2g2Source C&nbsp;&nbsp;&nbsp;&nbsp;a3b3Customer 1d3e3f3g3Source A&nbsp;&nbsp;&nbsp;&nbsp;a3b3Customer 1d3e3f3g3Source B&nbsp;&nbsp;&nbsp;&nbsp;a3b3Customer 1d3e3f3g3Source C&nbsp;&nbsp;&nbsp;&nbsp;a4b4Customer 2d4e4f4g4Source A&nbsp;&nbsp;&nbsp;&nbsp;a4b4Customer 2d4e4f4g4Source B&nbsp;&nbsp;&nbsp;&nbsp;a4b4Customer 2d4e4f4g4Source C&nbsp;&nbsp;&nbsp;&nbsp;a5b5Customer 2d5e5f5g5Source A&nbsp;&nbsp;&nbsp;&nbsp;a5b5Customer 2d5e5f5g5Source B&nbsp;&nbsp;&nbsp;&nbsp;a5b5Customer 2d5e5f5g5Source C&nbsp;&nbsp;&nbsp;&nbsp;a6b6Customer 2d6e6f6g6Source A&nbsp;&nbsp;&nbsp;&nbsp;a6b6Customer 2d6e6f6g6Source B&nbsp;&nbsp;&nbsp;&nbsp;a6b6Customer 2d6e6f6g6Source C&nbsp;&nbsp;&nbsp;&nbsp;a7b7Customer 2d7e7f7g7Source A&nbsp;&nbsp;&nbsp;&nbsp;a7b7Customer 2d7e7f7g7Source B&nbsp;&nbsp;&nbsp;&nbsp;a7b7Customer 2d7e7f7g7Source C&nbsp;&nbsp;&nbsp;&nbsp;a8b8Customer 3d8e8f8g8Source A&nbsp;&nbsp;&nbsp;&nbsp;a8b8Customer 3d8e8f8g8Source B&nbsp;&nbsp;&nbsp;&nbsp;a8b8Customer 3d8e8f8g8Source C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;And this is the end result I am looking for:aaabbbCustomerdddeeefffggg2020 A2020 B2020 C2021 A2021 B2021 C2022 A2022 B2022 C2023 A2023 B2023 Ca1b1Customer 1d1e1f1g1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;a2b2Customer 1d2e2f2g2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;a3b3Customer 1d3e3f3g3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;a4b4Customer 2d4e4f4g4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;a5b5Customer 2d5e5f5g5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;a6b6Customer 2d6e6f6g6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;a7b7Customer 2d7e7f7g7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;a8b8Customer 3d8e8f8g8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Thank you!

Forum Discussion

Transform multiple columns based on values in another column

7 Replies

Related content

Transform columns / type

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

Table Transformation

Transforming multiple columns

Transform Column / Type

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