IF THEN code branch in M

[macfuller]

I need to branch code in M, and I found Chris Webb's helpful blog post here. However, I don't want an ELSE clause in the code but M is demanding one, so I didn't know if there's a way to "dummy" a line so that I can continue execution.

I have large files with years of data that take forever to load, but for testing purposes I'd like to just take the last x days of my source files. I have a parameter table in the spreadsheet with an Override value (Y/N) and the days of history I want to load. The function fnGetWorkbookParameter will call the respective values.

My M code is thus:
...
AddConvFactor -= Table.AddColumn(...)

IsOverride = fnGetWorkbookParameter("Data History Override"),
OverrideDuration = fnGetWorkbookParameter("Days History"),
IfDateOverride = if IsOverride = "Y"
then Table.SelectRows(AddConvFactor, each Date.IsInPreviousNDays([PO Date], OverrideDuration))
else "No change",

ChangeConvFactorType = Table.TransformColumnTypes(IfDateOverride, ...)
...

In the "true" portion of my IF clause I can reference the prior part of the query up to step AddConvFactor, and the next step outside the IF clause references that step. But if I run my code without the override I have to use the ELSE clause with a dummy statement I don't want to use.

I'm not sure if there is a way to not need the ELSE clause, or what I should put there. Just set a variable I don't care about?

 

[Matt Allington]

You have to use else. If you don't need else, then you don't need if. In otherwords, if there is no alternative to the first part of the question "if", then why do you need it?

 

[macfuller]

Well, there are plenty of VBA instances where there's an If-Then without an else, though of course M isn't VBA. I have a default flow of 2 1/2 years of data and a "test" flow of 30 days of data. I haven't found the code in M to remove a filter, only to add it, so I don't know the reverse of then Table.SelectRows(AddConvFactor, each Date.IsInPreviousNDays([PO Date], OverrideDuration)). But there wasn't a filter on PO Date anyway in the default flow - so I don't want to do anything on the PO Date filter for the default flow, just for the test flow. Is there a way to do that without an IF?

 

[Matt Allington]

You could create a staging query, create a new query that selects those in the last N days, then do a left anti join against the original list to get the ones not in the last N days. There may be a better way, don't know

 

[MarcelBeug]

It looks to me there is a misunderstanding about M's immutability, i.e. no value is ever changed, only new values are created.

In this case, starting with full table "AddConvFactor": it is not the case that there will be a new version of "AddConvFactor" with selected records, but there will be a new table named "IfDateOverride".
Logically, this table should be one of: the full table or the table with selected records.

Ergo: after else I would expect AddConvFactor.
So the complete statement will be like: newtable = if test then select records else full table.

 

[macfuller]

I don't want to overthink this. If I'm in Test mode I want to add an extra step, filtering the full 2.5 years of data down to 30 days. If I'm in production mode don't execute that one step. Everything else is the same. Here's the full code

let
SrcFileName = "3PL_AP_SPEND.csv",
FileSource = fnGetWorkbookParameter("Data Source Folder Path") & SrcFileName,
Source = Csv.Document(File.Contents(FileSource),[Delimiter=",",Encoding=1252]),
PromoteHeaders = Table.PromoteHeaders(Source),
RenamePaymentDate = Table.RenameColumns(PromoteHeaders,{{"Date", "Payment Date"}}),
ChangeColumnTypes = Table.TransformColumnTypes(RenamePaymentDate,{{"Dept", type text}, {"Vendor Name", type text}, {"Vendor", type text}, {"Descr", type text}, {"Sum of Monetary Amount", Currency.Type}, {"Account", type text}, {"Unit", type text}, {"PO No.", type text}, {"Line", Int64.Type}, {"Sched Num", Int64.Type}, {"PO Distr Line", Int64.Type}, {"Descr_1", type text}, {"Payment Date", type date}, {"Invoice Date", type date}, {"Payment Amount", Currency.Type}, {"Sum Merchandise Amt", Currency.Type}, {"Sum Freight", Currency.Type}}),

IsOverride = fnGetWorkbookParameter("Data History Override"),
OverrideDuration = fnGetWorkbookParameter("Days History"),

IfDateOverride = if IsOverride = "Y"
then Table.SelectRows(ChangeColumnTypes, each Date.IsInPreviousNDays([Invoice Date], OverrideDuration))
else "No change",

RenameColumns = Table.RenameColumns(IfDateOverride,{{"Sum of Monetary Amount", "Voucher Amount"}, {"Payment Amount", "Check Amount"}, {"Invoice Date", "Accounting Date"}, {"Sum Merchandise Amt", "Merchandise Amt"}, {"Sum Freight", "Freight Charges"}}),
AddForeignKey = Table.AddColumn(RenameColumns, "FK_PO", each [#"PO No."] & "_" & Number.ToText([Line]) & "_" & Number.ToText([Sched Num]) & "_" & Number.ToText([PO Distr Line]))
in
AddForeignKey

 

[MarcelBeug]

So simply adjust your code to:

let
    SrcFileName = "3PL_AP_SPEND.csv",
    FileSource = fnGetWorkbookParameter("Data Source Folder Path") & SrcFileName,
    Source = Csv.Document(File.Contents(FileSource),[Delimiter=",",Encoding=1252]),
    PromoteHeaders = Table.PromoteHeaders(Source),
    RenamePaymentDate = Table.RenameColumns(PromoteHeaders,{{"Date", "Payment Date"}}),
    ChangeColumnTypes = Table.TransformColumnTypes(RenamePaymentDate,{{"Dept", type text}, {"Vendor Name", type text}, {"Vendor", type text}, {"Descr", type text}, {"Sum of Monetary Amount", Currency.Type}, {"Account", type text}, {"Unit", type text}, {"PO No.", type text}, {"Line", Int64.Type}, {"Sched Num", Int64.Type}, {"PO Distr Line", Int64.Type}, {"Descr_1", type text}, {"Payment Date", type date}, {"Invoice Date", type date}, {"Payment Amount", Currency.Type}, {"Sum Merchandise Amt", Currency.Type}, {"Sum Freight", Currency.Type}}),

    IsOverride = fnGetWorkbookParameter("Data History Override"),
    OverrideDuration = fnGetWorkbookParameter("Days History"),

    IfDateOverride = if IsOverride = "Y"
        then Table.SelectRows(ChangeColumnTypes, each Date.IsInPreviousNDays([Invoice Date], OverrideDuration))
        else [B]ChangeColumnTypes[/B],

    RenameColumns = Table.RenameColumns(IfDateOverride,{{"Sum of Monetary Amount", "Voucher Amount"}, {"Payment Amount", "Check Amount"}, {"Invoice Date", "Accounting Date"}, {"Sum Merchandise Amt", "Merchandise Amt"}, {"Sum Freight", "Freight Charges"}}),
    AddForeignKey = Table.AddColumn(RenameColumns, "FK_PO", each [#"PO No."] & "_" & Number.ToText([Line]) & "_" & Number.ToText([Sched Num]) & "_" & Number.ToText([PO Distr Line]))
 in
    AddForeignKey

 

[macfuller]

Thanks. What are those line identifiers like ChangeColumnType called? Obviously I'm unclear on the concept and my copy of M is for Data Monkey doesn't really go into detail. I was concerned that if I put that line there it would execute the line again (rather than just pointing to it?) and take a hit on performance.

 

[MarcelBeug]

Those identifiers are referred to as "step names" or just "identifier".
Each identifier represents a value that is the result of evaluating the code behind the equal sign.
A value can be anything from a single number or text, to a table with multiple levels of nested lists, records or even functions.
From this perspective, a query is just a set of statements in which values are transformed into new values, with the final query result represented by the code after "in" (typically the last step name).

You should also be aware that Power Query has the concept of lazy evaluation, meaning that code will only be evaluated if required for the end result.
That is: when the query is run or refreshed; inside the query editor, each step must be evaluated (as you can step through all steps).
Also note that there might be a big difference between the steps of your query and actual evaluation of the entire query code.
E.g. in case of Query Folding / Direct Query, all query steps may actually be translated into 1 SQL-Statement that is executed at the database server.