cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lgs1983 Regular Visitor
Regular Visitor

Tidying Up Code / Simplifying Code

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

1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: Tidying Up Code / Simplifying Code

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

View solution in original post

4 REPLIES 4
Nathaniel_C Super Contributor
Super Contributor

Re: Tidying Up Code / Simplifying Code

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 Regular Visitor
Regular Visitor

Re: Tidying Up Code / Simplifying Code

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

Nathaniel_C Super Contributor
Super Contributor

Re: Tidying Up Code / Simplifying Code

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

View solution in original post

lgs1983 Regular Visitor
Regular Visitor

Re: Tidying Up Code / Simplifying Code

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

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 446 members 3,808 guests
Please welcome our newest community members: