Forum Discussion
Complex Pivoting
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 |
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.
4 Replies
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.
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?
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.
Totally what I needed! Thank you ~~~~
