Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

M Language - unpivot issue

Hi I am trying to Unpivot a General Ledger Table in Power Query

 

The original format is

 

CCACCP1 ActualP2 ActualP1 BudgetP2 Budget
40401204221.9859936.526511.0998413.892745
30401202429.83816.8538633.8378647.388504

 

* only first 2 rows...

 

My end goal is this

 

CCACCMonthActualBudget
3040110July774644.7788141.4
3040110August832047.7775587.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

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thanks Matt

 

It worked

 

Regards

 

Pedro

View solution in original post

4 REPLIES 4

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Thanks, Matt.

 

I found on google.  

 

The link above is not working for me.

 

Regards

 

Pedro

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.