• 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

Dynamic Column Name encounter error|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Dynamic Column Name encounter error|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 QueryDynamic Column Name encounter error
sp_PrintTopic sp_TopicIcon
Dynamic Column Name encounter error
Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
1
November 18, 2020 - 11:57 pm
sp_Permalink sp_Print

Hi Catalin

I am trying to make the first column dynamic,  i.e.  whenever I rename in the source,  it will not affect the Applied steps code,  however  seems like it doesn't work for Custom column,  or did I miss out anything in the M code

Thank you !

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Chg_Type = Table.TransformColumnTypes(Source,{{Table.ColumnNames(Source){0}, type date}}),
#"Added Custom" = Table.AddColumn(Chg_Type, "CY", each Text.End ( Text.From ( Date.Year (Table.ColumnNames(Source){0})) , 2 ))
in
#"Added Custom"

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
November 19, 2020 - 3:17 am
sp_Permalink sp_Print

What's the purpose of the #"Added Custom" step?

Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
3
November 19, 2020 - 11:43 am
sp_Permalink sp_Print

Hi Catalin, 

This #"Added Custom"  is current year in yy format,  there will be next year and previous year,   this query ultimate goal is to final out Fascial year.

wanted to make the query dynamic so that whenever there is any change in the original source header it will not affect the code,  i.e.  don't need to amend the new col names

thank you !

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
November 19, 2020 - 8:59 pm
sp_Permalink sp_Print

Table.ColumnNames(Source){0} will return the Name of the first column from the table Source. (Table.ColumnNames returns a list of column ... names)

If you want to refer to the first row from the DateColumn column, use:
Source[DateColumn]{0}

Or, if your parameter table is... dynamic and the headers will change (which by the way sounds weird, a parameter table should be static), you can refer to the first cell from the first column:

Table.ToColumns(Source){0}{0}

Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
5
November 20, 2020 - 12:09 am
sp_Permalink sp_Print

Hi Catalin,

Attached is my complete codes

actually what I hope to achieve is whenever there is any changes in the source header,  for e.g.  rename from DateColumn to Start_Date

All the custom columns variables will not affected,   I had already taken care the dynamic for Chg_Type by putting Table.ColumnNames(Source){0} in a parameter header1,   is it possible to make those highlighted one dynamic , Table.ToColumns(Chg_Type){0}{0},  Table.ToColumns(Chg_Type){0}{1},  using loop or other methods,   if not everytime I got to change all the previous header name to the new one.

Apologise I may not explain clearly in my previous threads

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Header1 = Table.ColumnNames(Source){0},
    Chg_Type = Table.TransformColumnTypes(Source,{{Header1, type date}}),
    LastYr = Table.AddColumn(Chg_Type, "LY", each Text.From ( Date.Year ([DateColumn])-1)),
    CurrentYr = Table.AddColumn(LastYr, "CY", each Text.From ( Date.Year ([DateColumn]))),
    #"Changed Type" = Table.TransformColumnTypes(CurrentYr,{{"LY", type text}, {"CY", type text}}),
    FY = Table.AddColumn(#"Changed Type", "FY", each if Date.Month([DateColumn]) < Date.Month(#date(Date.Year ([DateColumn]), 4, 1)) and               Text.From(Date.Year([DateColumn])) = [CY] then "FY "&[LY] else "FY "& [CY])
in
    FY

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Alan Sidman, Ngoc Tinh, Dieneba NDIAYE, Alexandra Radu, Natasha Smith, Monique Roussouw
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:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6216
Posts: 27250

 

Member Stats:
Guest Posters: 49
Members: 31897
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.