Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lgs1983
Helper I
Helper I

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

1 ACCEPTED SOLUTION

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

 

View solution in original post

10 REPLIES 10
AnkitBI
Solution Sage
Solution Sage

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)))})

@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?

 

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

 

@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"})

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

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

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.

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   

Yes Expected the same requirement. Better to be double sure. I have come with below. Right now it's in individual steps, will try to tidy once get free time. #"Added Conditional Column" caters to first 3 month. You can add more.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tKkrNS65U0lFyiwQSXol5QYklqSBuahKU5ZtYBGbF6kQrhQa7gJUaWgIpAz1DI2MTUzNzCzjPDIUHlUPWaGRAisZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _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),
    #"Get List" = List.Skip(Table.ColumnNames(#"Changed Type"),2),
    #"Converted to Table" = Table.FromList(#"Get List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Duplicated Column" = Table.DuplicateColumn(#"Converted to Table", "Column1", "Column1 - Copy"),
    #"Extracted First Characters" = Table.TransformColumns(#"Duplicated Column", {{"Column1 - Copy", each Text.Start(_, 3), type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Extracted First Characters", "Custom", each if [#"Column1 - Copy"] = "Jan" then 1 else if [#"Column1 - Copy"] = "Feb" then 2 else if [#"Column1 - Copy"] = "Mar" then 3 else null, type number),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "New Name", each Date.ToText(Date.EndOfMonth(#date(Int64.From(Text.Combine({"20" & FY})),[Custom],1)),"YYYY-MM-DD")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Column1", "New Name"}),
    #"Lists of List" = Table.ToRows(#"Removed Other Columns"),
    #"Final  Output" = Table.RenameColumns(#"Changed Type",#"Lists of List")
in
    #"Final  Output"

Initial State:

 

 

CurrencyFYJanRateFebRateMarRate
USDFY190.1234567890.1236567890.123456789
USDFY200.1234567890.1236567890.123456789

 

Final State

 

CurrencyFY2019-01-312019-02-282019-03-31
USDFY190.1234567890.1236567890.123456789
USDFY200.1234567890.1236567890.123456789

Thank you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors