Transform multiple columns based on values in another column

[Anonymous]

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 Excel. How can I do the following?

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. 

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.

Really appreciate your help. Thanks!

[Anonymous]

** 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

a1

b1

Customer 1

d1

e1

f1

g1

a2

b2

Customer 1

d2

e2

f2

g2

a3

b3

Customer 1

d3

e3

f3

g3

a4

b4

Customer 2

d4

e4

f4

g4

a5

b5

Customer 2

d5

e5

f5

g5

a6

b6

Customer 2

d6

e6

f6

g6

a7

b7

Customer 2

d7

e7

f7

g7

a8

b8

Customer 3

d8

e8

f8

g8

Thank you!

[adudani]

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]

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

...

a1

Customer 1

a2

Customer 1

[adudani]

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]

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

[foodd]

Hello @Anonymous , and thank you for sharing a question with the Community.  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/963216

Please 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#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

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.  Proud to be a Super User!

[Anonymous]

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