Transpose table with three fields to transpose and two value columns

Question

Hello,&nbsp;I have imported a table with 5 columns:&nbsp;YEAR_QUARTER, YEAR_QUARTER_ORDER, LEVEL, PERCENTAGE_QUARTER and PERCENTAGE_YEAR&nbsp;YEAR_QUARTERYEAR_QUARTER_ORDERLEVELPERCENTAGE_QUARTERPERCENTAGE_YEAR2019 Q120191EAST97,5697,562019 Q120191WEST95,8795,872019 Q120191TOTAL96,4696,462019 Q220192EAST99,3998,482019 Q220192WEST100,7498,312019 Q220192TOTAL100,2798,372019 Q320193EAST100,1898,492019 Q320193WEST98,7497,952019 Q320193TOTAL99,2498,142019 Q420194EAST95,9097,322019 Q420194WEST94,3698,372019 Q420194TOTAL94,9098,002020 Q120201EAST97,5097,502020 Q120201WEST98,8698,862020 Q120201TOTAL98,3898,382020 Q220202EAST95,7096,602020 Q220202WEST101,50100,182020 Q220202TOTAL99,4798,93&nbsp;I need to transpose this table in such a way that I get one row per value of&nbsp;YEAR_QUARTER, 3 columns - one per LEVEL - for&nbsp;PERCENTAGE_QUARTER and&nbsp;3 columns - one per LEVEL - for&nbsp;PERCENTAGE_YEAR.&nbsp;YEAR_QUARTERYEAR_QUARTER_ORDERPERCENTAGE_QUARTER_EASTPERCENTAGE_QUARTER_WESTPERCENTAGE_QUARTER_TOTALPERCENTAGE_YEAR_EASTPERCENTAGE_YEAR_WESTPERCENTAGE_YEAR_TOTAL2019 Q12019197,5695,8796,4697,5695,8796,462019 Q22019299,39100,74100,2798,4898,3198,372019 Q320193100,1898,7499,2498,4997,9598,142019 Q42019495,9094,3694,9097,3298,3798,002020 Q12020197,5098,8698,3897,5098,8698,382020 Q22020295,70101,5099,4796,60100,1898,93&nbsp;How can I do this?&nbsp;Thanks&nbsp;R.W.

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,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]&amp;"_"&amp;[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 RegardsRena

Forum Discussion

Transpose table with three fields to transpose and two value columns

2 Replies

Related content

Help Transposing

Transpose/ Transform Rows to Columns

Transpose data

Transpose table column values

Transpose Title Row ONLY

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

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