Forum Discussion
lgs1983
6 years agoHelper 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 ...
- 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
AnkitBI
6 years agoSolution Sage
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 |
lgs1983
6 years agoHelper I
Thank you