Tidying Up Code / Simplifying Code

[lgs1983]

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

[Nathaniel_C]

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

[lgs1983]

@Nathaniel_C Thanks. How does one go about grouping these together? What's the structure? 

[Nathaniel_C · Accepted 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

[lgs1983]

@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.