Forum Discussion

lgs1983's avatar
lgs1983
Helper I
6 years ago
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 ...
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    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