Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi I am trying to Unpivot a General Ledger Table in Power Query
The original format is
CC | ACC | P1 Actual | P2 Actual | P1 Budget | P2 Budget |
40 | 40120 | 4221.985 | 9936.52 | 6511.099 | 8413.892745 |
30 | 40120 | 2429.83 | 816.853 | 8633.837 | 8647.388504 |
* only first 2 rows...
My end goal is this
CC | ACC | Month | Actual | Budget |
30 | 40110 | July | 774644.7 | 788141.4 |
30 | 40110 | August | 832047.7 | 775587.5 |
* only first 2 rows
P1= July
P2= August
I managed to do that, however, when I pivot in one of the steps
#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute.2]), "Attribute.2", "Value", List.Sum),
it only allowed me to aggregate SUM and if I select do not aggregate it gave me an error. Does anyone know why?
Here are all the steps recorded in M from the Power Query editor.
let Source = Excel.Workbook(File.Contents("C:\Users\User\Desktop\Test Unpivot.xlsx"), null, true), #"Uppivot Test_Sheet" = Source{[Item="Uppivot Test",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Uppivot Test_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CC", Int64.Type}, {"ACC", Int64.Type}, {"P1 Actual", type number}, {"P2 Actual", type number}, {"P1 Budget", type number}, {"P2 Budget", type number}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"CC", "ACC"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "Period"}}), #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute.2]), "Attribute.2", "Value", List.Sum), #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column","P1","July",Replacer.ReplaceText,{"Period"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","P2","August",Replacer.ReplaceText,{"Period"}) in #"Replaced Value1"
Can it be done? i.e. do not aggregate when pivot back to get one column for Budget and one for Actual?
Also, I am open to suggestions if that can be done more efficiently.
Best Regards
Pedro
Solved! Go to Solution.
On a hunch, try adding an index column as the very first step. When you repivot data, you need a unique row identifier, so if you add that first, you can then use it when you put it back together again.
Thanks Matt
It worked
Regards
Pedro
great 🙂 If you want to learn more about Power Query, you may be interested in my online training course http://xbi.com.au/pqt
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |