Complex Pivoting

[andya_utah]

I am trying to replicate results from an another ETL platform I utilize to arrive at a more complex piviot the BI seems to offer. I am new using BI.

I would like to go from a to b - if it is possible.. Any pointers would be appreciated...

A

ClassName	BeginYear	Assessment	Total	Percent
CCH 2 Duration	2023	Fall	125	80
CCH 2 Duration	2023	Spring	152	87
CCH 2 Duration	2024	Fall	107	68
CCH 2 Duration	2024	Spring	141	82

B

ClassName	BeginYear	Fall Total	Fall Percent	Spring Total	Spring Percent
CCH 2 Duration	2023	125	80	152	87
CCH 2 Duration	2024	107	68	141	85

[andya_utah]

Totally what I needed! Thank you ~~~~

[Irwan · Accepted solution]

hello @andya_utah 

please check if this accomodate your need.

if you want to do this in PQ.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcnb2UDBScCktSizJzM9T0lEyMjAyBlJuiTk5QMrQyBRIWhgoxergVhtcUJSZlw5SbWoEUm2OU7UJkskG5kDSzAKvWoTJJoYgk42UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClassName = _t, BeginYear = _t, Assessment = _t, Total = _t, Percent = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ClassName", type text}, {"BeginYear", Int64.Type}, {"Assessment", type text}, {"Total", Int64.Type}, {"Percent", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ClassName", "BeginYear", "Assessment"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Assessment", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
#"Pivoted Column"

1. Unpivot Total and Percent

2. Merge Assessment and Attribut

3. Pivot Merged column using Value column as value in pivot

if you want to do this in PBI.

following after merged as above, load the data into PBI, the use matrix visual

Hope this will help.

Thank you.

[WishAskedSooner]

Nothing complex at all when using PowerQuery. Just bring your table into PBI, and instead of clicking Load, click Transform and edit the query. The UI is your friend.

[andya_utah]

Thank you for the response - but the "pivot column" function in power query does not allow me to arrive at the result I posted above. Care to enlighten me more as how to accomplish?