Forum Discussion
Rename Columns based on end of month using M code
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
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:
Currency FY JanRate FebRate MarRate USD FY19 0.123456789 0.123656789 0.123456789 USD FY20 0.123456789 0.123656789 0.123456789 Final State
Currency FY 2019-01-31 2019-02-28 2019-03-31 USD FY19 0.123456789 0.123656789 0.123456789 USD FY20 0.123456789 0.123656789 0.123456789