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 All,
As you'll see from the below M code I'm very new to the language. What I was wondering is if there is a way to tidy up the below code as it's pretty much doing the same thing in places e.g. it's changing the format of the date to YYYY-MM-DD for each month, it's replacing the existing values with the new variables.
let Source = Excel.Workbook(File.Contents("C:\Users\shawlg\Desktop\Active Project Work\ExchangeRateTest.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"MDSCurrencyCode", type text}, {"JanRate", type number}, {"FebRate", type number}}), FY = Text.End(#"Changed Type"[FileYear]{0},2), //January Change #"DateJanVar" = Date.EndOfMonth(#date(Int64.From(Text.Combine({"20" & FY})),1,1)), #"DateJanFormat" = Date.ToText(DateJanVar,"YYYY-MM-DD"), JanChg = Table.ReplaceValue(Sheet1_Sheet,"JanRate",#"DateJanFormat",Replacer.ReplaceValue,{"Column4"}), //February Change #"DateFebVar" = Date.EndOfMonth(#date(Int64.From(Text.Combine({"20" & FY})),2,1)), #"DateFebFormat" = Date.ToText(DateFebVar,"YYYY-MM-DD"), FebChg = Table.ReplaceValue(JanChg,"FebRate",#"DateFebFormat",Replacer.ReplaceValue,{"Column5"}), //March Date Change #"DateMarVar" = Date.EndOfMonth(#date(Int64.From(Text.Combine({"20" & FY})),3,1)), #"DateMarFormat" = Date.ToText(DateMarVar,"YYYY-MM-DD"), MarChg = Table.ReplaceValue(FebChg,"MarRate",#"DateMarFormat",Replacer.ReplaceValue,{"Column6"}) in MarChg
Background
The code is being used to grab the last two digits of the year from a file ingested by Power BI. It then applies that alongside "20" to form the year and create the end of month variable to replace the JanRate to 2019-1-31, FebRate to 2019-02-28 etc.
Any assistance is always greatly appreciated.
Thanks
Solved! Go to Solution.
Hi @lgs1983 ,
It is the order in which you perform the steps. I would say create a bogus table and work on it by trial basis. Change a column name, then change a type or add a column, then look at doing two column name changes in a row or two type changes, and see how it combines them. A lot of times I will use a form, like add a conditional column to see the syntax, and then change it to my requirement. If you come from a programming background, this is different. Most people just follow the steps. BTW if you used a form to enter something there will be a gear on that step.
That being said, if you are interested in learning M, there are websites and blogs out there. Once you get a handle on the form, it won't be long before you will be modifing your steps. This code doesn't usually go too long, as you just transforming the data.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @lgs1983 ,
Power Query does aggregate certain commands if they are together. So changing the name of the column, and changing the type can be done at the end, and will be just two lines of code. You might try that with the other functions as well, but you lose your //notes by month.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
@Nathaniel_C Thanks. How does one go about grouping these together? What's the structure?
Hi @lgs1983 ,
It is the order in which you perform the steps. I would say create a bogus table and work on it by trial basis. Change a column name, then change a type or add a column, then look at doing two column name changes in a row or two type changes, and see how it combines them. A lot of times I will use a form, like add a conditional column to see the syntax, and then change it to my requirement. If you come from a programming background, this is different. Most people just follow the steps. BTW if you used a form to enter something there will be a gear on that step.
That being said, if you are interested in learning M, there are websites and blogs out there. Once you get a handle on the form, it won't be long before you will be modifing your steps. This code doesn't usually go too long, as you just transforming the data.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
@Nathaniel_C Thank you. Will have a play around and do some research into the inner workings of M as going to be useful in the long run.
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.