• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Is it possible to calculate without using the columns names but relative positions Continuation|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Is it possible to calculate without using the columns names but relative positions Continuation|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryIs it possible to calculate without…
sp_PrintTopic sp_TopicIcon
Is it possible to calculate without using the columns names but relative positions Continuation
Avatar
Marc Bergmans
Member
Members
Level 0
Forum Posts: 24
Member Since:
February 19, 2021
sp_UserOfflineSmall Offline
1
March 3, 2021 - 7:13 pm
sp_Permalink sp_Print

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.Printscreen-PQ-1.pngImage Enlarger

Printscreen-PQ-2.pngImage Enlarger
Printscreen-PQ-2b.pngImage Enlarger
Printscreen-PQ-3.pngImage Enlarger

Marc

sp_PlupAttachments Attachments
  • sp_PlupImage Printscreen-PQ-1.png (149 KB)
  • sp_PlupImage Printscreen-PQ-2.png (115 KB)
  • sp_PlupImage Printscreen-PQ-2b.png (26 KB)
  • sp_PlupImage Printscreen-PQ-3.png (101 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
March 3, 2021 - 7:42 pm
sp_Permalink sp_Print

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"

Avatar
Marc Bergmans
Member
Members
Level 0
Forum Posts: 24
Member Since:
February 19, 2021
sp_UserOfflineSmall Offline
3
March 3, 2021 - 10:14 pm
sp_Permalink sp_Print

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.

sp_PlupAttachments Attachments
  • sp_PlupImage PQ-Difference.png (117 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
March 4, 2021 - 12:00 am
sp_Permalink sp_Print

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])

Avatar
Marc Bergmans
Member
Members
Level 0
Forum Posts: 24
Member Since:
February 19, 2021
sp_UserOfflineSmall Offline
5
March 4, 2021 - 9:51 pm
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
March 5, 2021 - 12:24 am
sp_Permalink sp_Print sp_EditHistory

Here is the query:

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),
#"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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Jessica Stewart, Mark Carlson, Calvin Richardson
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6201
Posts: 27185

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.