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