Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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)))})
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"})
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 |
Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.