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 ...
Need Help
  • 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

     

lgs1983's avatar
lgs1983
Helper I
6 years ago

AnkitBI 

 

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"})
AnkitBI's avatar
AnkitBI
Solution Sage
6 years ago

Great, Awesome. Thanks for this question, had a good learning :)

  • lgs1983's avatar
    lgs1983
    Helper I
    6 years ago

    AnkitBI sorry to be a pain but how would I structure the code to do it for Feb and Mar too for example?

    • AnkitBI's avatar
      AnkitBI
      Solution Sage
      6 years ago

      Share sample input and expected result . The code I shared has Initial ColumnName and Month 01 in #date HardCoded. We will need to make it dynamic.

      • lgs1983's avatar
        lgs1983
        Helper I
        6 years ago

        So is similar as before really,

         

        EXISTING

        Currency              FY            JanRate                        FebRate                       MarRate                       AprRate

        USD                     FY19        0.123456789                0.123456789                0.123456789                0.123456789                

         

        DESIRED OUTCOME

        Currency              FY            2019-01-31                 2019-02-28                  2019-03-31                  2019-04-30                       

        USD                     FY19        0.123456789                0.123456789                0.123456789                0.123456789