Transpose table with three fields to transpose and two value columns

[Anonymous]

Hello,

I have imported a table with 5 columns: YEAR_QUARTER, YEAR_QUARTER_ORDER, LEVEL, PERCENTAGE_QUARTER and PERCENTAGE_YEAR

YEAR_QUARTER	YEAR_QUARTER_ORDER	LEVEL	PERCENTAGE_QUARTER	PERCENTAGE_YEAR
2019 Q1	20191	EAST	97,56	97,56
2019 Q1	20191	WEST	95,87	95,87
2019 Q1	20191	TOTAL	96,46	96,46
2019 Q2	20192	EAST	99,39	98,48
2019 Q2	20192	WEST	100,74	98,31
2019 Q2	20192	TOTAL	100,27	98,37
2019 Q3	20193	EAST	100,18	98,49
2019 Q3	20193	WEST	98,74	97,95
2019 Q3	20193	TOTAL	99,24	98,14
2019 Q4	20194	EAST	95,90	97,32
2019 Q4	20194	WEST	94,36	98,37
2019 Q4	20194	TOTAL	94,90	98,00
2020 Q1	20201	EAST	97,50	97,50
2020 Q1	20201	WEST	98,86	98,86
2020 Q1	20201	TOTAL	98,38	98,38
2020 Q2	20202	EAST	95,70	96,60
2020 Q2	20202	WEST	101,50	100,18
2020 Q2	20202	TOTAL	99,47	98,93

I need to transpose this table in such a way that I get one row per value of YEAR_QUARTER, 3 columns - one per LEVEL - for PERCENTAGE_QUARTER and 3 columns - one per LEVEL - for PERCENTAGE_YEAR.

YEAR_QUARTER	YEAR_QUARTER_ORDER	PERCENTAGE_QUARTER_EAST	PERCENTAGE_QUARTER_WEST	PERCENTAGE_QUARTER_TOTAL	PERCENTAGE_YEAR_EAST	PERCENTAGE_YEAR_WEST	PERCENTAGE_YEAR_TOTAL
2019 Q1	20191	97,56	95,87	96,46	97,56	95,87	96,46
2019 Q2	20192	99,39	100,74	100,27	98,48	98,31	98,37
2019 Q3	20193	100,18	98,74	99,24	98,49	97,95	98,14
2019 Q4	20194	95,90	94,36	94,90	97,32	98,37	98,00
2020 Q1	20201	97,50	98,86	98,38	97,50	98,86	98,38
2020 Q2	20202	95,70	101,50	99,47	96,60	100,18	98,93

How can I do this?

Thanks

R.W.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can make some transformations in Power Query Editor to achieve it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddI9CsMwDAXgq5TMGmRZ/hszZCuU0kCHkPtfo3atyA7Y08vwYV4kHcdCaNLjbRb4f5Xc1s+eIwVwXvOEEf1ulTqIQXNM99e+PovxwF6zWRJLXYMENpWMwHFCpYFBhMDVWjOxV4WCKQju+1rBtnUo1kQpkSb2GkOUDgGSm1AdQwKSvoY7y2K5G4ODhPVZSxN6NWCwfvBjPdUGLM9GQBRLeC0t6/spoOaYthlErzmm2iC3jJrNkli6zSBgvRqPE6qnYGpbWd0Yd3tgOYW8n/P8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YEAR_QUARTER = _t, YEAR_QUARTER_ORDER = _t, LEVEL = _t, PERCENTAGE_QUARTER = _t, PERCENTAGE_YEAR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YEAR_QUARTER", type text}, {"YEAR_QUARTER_ORDER", Int64.Type}, {"LEVEL", type text}, {"PERCENTAGE_QUARTER", Int64.Type}, {"PERCENTAGE_YEAR", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"PERCENTAGE_QUARTER", "PERCENTAGE_YEAR"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each [Attribute]&"_"&[LEVEL]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"LEVEL", "Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value", List.Sum)
in
    #"Pivoted Column"

Best Regards

Rena

[parry2k]

@Anonymous the unpivoted model is better , why not use matrix visual with Level on Column and value on the rows.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡