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 code generated from that and came up with the following which failed as a 'comma was expected'.

 

= Table.RenameColumns(#"Changed Type",{"JanRate"}, Date.EndOfMonth(#date(2019, 1, 1)})

Any help on this would be much appreciated

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

     

10 Replies

  • AnkitBI's avatar
    AnkitBI
    Solution Sage
    6 years ago

    Hi - Can you share sample data and clarify the exact requirement. Is it to change column Name JanRate to JanRate 31-01-2019, then you can use below formula. 

     

    = Table.RenameColumns(#"Changed Type",{"JanRate","JanRate " & Text.From(Date.EndOfMonth(#date(2019, 1, 1)))})

    • lgs1983's avatar
      lgs1983
      Helper I
      6 years ago

      AnkitBI 

       

      Yep, the formula you have there is the correct one. It's kind of manifested itself now though as what is now an additonal required is to get the last two digits of the year from another field.

       

      So, as an example:

       

      EXISTING

      Currency              FY            JanRate

      USD                     FY19        0.123456789

       

      DESIRED OUTCOME

      Currency              FY            2019-01-31

      USD                     FY19        0.123456789

       

      I was playing around and I was able to get this far but got the below error message

       

      Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

       

      = Table.AddColumn(#"Added Custom", "Custom.1", each Date.EndOfMonth(#date(Number.FromText(Text.Combine({"20", Text.End([FileYear],2)})),1,1)))

       

      Any ideas?

       

      • AnkitBI's avatar
        AnkitBI
        Solution Sage
        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