Forum Discussion
Solved
Rename Columns based on end of month using M code
Hi, I can't say I know much about M code at all but have been tasked with changing the headers to the end of the month date for each calendar month. I tried changing the name and using the M ...
Try Below Code. One Important thing, dynamic value "FY" will be always picked based on the first row of Source.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tKkrNS65U0lFyiwQSXol5QYklqUqxOtFKocEuYGFDSyBloGdoZGxiamZuYakUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Currency", type text}, {"JanRate", type number}}), FY = Text.End(#"Changed Type"{0}[FY],2), Custom1 = Date.ToText(Date.EndOfMonth(#date(Int64.From(Text.Combine({"20" & FY})),1,1)),"YYYY-MM-DD"), Custom2 = Table.RenameColumns(#"Changed Type",{"JanRate",Custom1}) in Custom2
Try Below Code. One Important thing, dynamic value "FY" will be always picked based on the first row of Source.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tKkrNS65U0lFyiwQSXol5QYklqUqxOtFKocEuYGFDSyBloGdoZGxiamZuYakUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Currency", type text}, {"JanRate", type number}}),
FY = Text.End(#"Changed Type"{0}[FY],2),
Custom1 = Date.ToText(Date.EndOfMonth(#date(Int64.From(Text.Combine({"20" & FY})),1,1)),"YYYY-MM-DD"),
Custom2 = Table.RenameColumns(#"Changed Type",{"JanRate",Custom1})
in
Custom2
Worked great, thank you.
I was also able to do it by adding a decimal format parameter in but obviously involves manual intervention come the year change
#"Replaced Value" = Table.ReplaceValue(Sheet1_Sheet,"JanRate",Date.ToText(Date.EndOfMonth(#date(Year, 1, 1)),"YYYY-MM-DD"),Replacer.ReplaceValue,{"Column4"})Great, Awesome. Thanks for this question, had a good learning :)