Log In
Register
Home
February 19, 2021
Offline
This question is the continuation of my previous post (working with dynamic column names or relative references to columns in Excel Power Query.
I think i'm to new in Excel Power Query to understand the previous answer and to bring it to a good end.
I still don't succeed in calculating with columns in Power Excel Query which names change every refresh.
Therefor i want to ask if it is possible to solve this problem for me.
I hoped that your solution will help me to understand better Excel Power Query and the M-language.
The thing that i that i need is that in the sheet Grouped two columns are added.
One with sum of the columns A and B en one with the difference between A and B.
But the names of A en B can change every refresh.
So the formula to process the sum and difference need to work with relative columns references or dynamic columns names.
In attachment you can find the worksheet and some printscreens
If you need more information, please ask.
Marc
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
One simple trick:
After you group the data, demote headers, the button for "Use First Row as Headers" has a dropdown with the opposite action: "Use Headers as First Row".
This will move the current headers down, the headers will become: Column1, Column2, and so on.
You can then use simple formulas referring to [Column3]+[Column4] for example.
After calculations, move headers back up with Use First Row as headers.
let
Source = Excel.CurrentWorkbook(){[Name="Ophalen_Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"L", type text}, {"ID", type text}, {"ID2", type text}, {"P", type text}, {"ART", Int64.Type}, {"AC", Int64.Type}, {"GD1", Int64.Type}, {"GD2", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "ID2", "P"}, {{"Tot", each List.Sum([ART]), type nullable number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[P]), "P", "Tot", List.Sum),
#"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
#"Added Custom" = Table.AddColumn(#"Demoted Headers", "Custom", each try [Column3]+[Column4] otherwise null),
#"Promoted Headers" = Table.PromoteHeaders(#"Added Custom", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column5", "A+B"}})
in
#"Renamed Columns"
February 19, 2021
Offline
Thank you for this answer and solutions.
I'm really not good enough to solve it.
So i have a next question.
I also have to Add a column with the subtraction of those two columns (for which the names differs every refresh).
I cold add the subtract formula in the code but i don't succeed in adding the header of this new column into the Promoted Headers section and in the Renamed Columns Section.
Can you please help me again.
I added the adapted worksheet and a printscreen of the substraction.
Thank you very much.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
The step Promote Headers points to Added Custom step, not the Added Difference step, so it ignores the step you added.
Change from:
= Table.PromoteHeaders(#"Added Custom", [PromoteAllScalars=true]) to:
= Table.PromoteHeaders(#"Added Difference", [PromoteAllScalars=true])
February 19, 2021
Offline
Okay, but i need both if i change Custom to Difference than i don' t have the Custom and the custom need to become Total.
I need to have two new added Headers namely Total and Difference.
So i think the two new header has to be added in the Table.PromteHeaders funtion or if this maybe it can be done in 2 or more steps but i don't know how this works.
Can you help me again please.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Here is the query:
Source = Excel.CurrentWorkbook(){[Name="Ophalen_Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"L", type text}, {"ID", type text}, {"ID2", type text}, {"P", type text}, {"ART", Int64.Type}, {"AC", Int64.Type}, {"GD1", Int64.Type}, {"GD2", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "ID2", "P"}, {{"Tot", each List.Sum([ART]), type nullable number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[P]), "P", "Tot", List.Sum),
#"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
#"Added Custom" = Table.AddColumn(#"Demoted Headers", "Custom", each try [Column3]+[Column4] otherwise null),
#"Added Difference" = Table.AddColumn(#"Added Custom", "Difference", each try [Column4]-[Column3] otherwise null),
#"Promoted Headers" = Table.PromoteHeaders(#"Added Difference", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column5", "Total"}})
in
#"Renamed Columns"
What you see in red is what I told you to change in previous message, and it's all I did now.
It returns the 2 new columns with sum and difference.
All you have to do now is to double click Column6 in PQ Editor to rename it to Difference.
I suggest you should try learning how power query works before diving into projects, it will be easier for you to understand how it works.
1 Guest(s)
