Log In
Register
Home
August 21, 2019
Offline
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"
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
What's the purpose of the #"Added Custom" step?
August 21, 2019
Offline
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 !
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
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}
August 21, 2019
Offline
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
1 Guest(s)
