Can you do if statements in Advanced Editor in Power Query?

[nchinbong]

Hi Everyone,

I am loading a Fiscal Reference Date Dim that has 3 years in it, the current year and 2 prior years. There is a column for relative year where the current year is 0, one prior is -1 and two prior is -2. My report typically would have 2 fiscal years except for a period of 3 weeks at the beginning of a new fiscal year where there would be 3 fiscal years. Currently when I load the Dim table, I filter out the second fiscal year. In the advanced editor, it looks like this:

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([RLTV_FISC_YR_NR] <> -2)),

I am wondering if there is a straight forward way to stick an if statement in there? For example, if today's month is November AND todays day is less than or equal to 15 then load all 3 years else do not load relative year -2.

I can think of a round about way of creating a table to determine which years to load and then joining the tables to get a single table. I just didn't want to create this kind of work around if there was a straight forward way of doing it in the Advnaced Editor.

Thanks in Advanced. I'm just getting started with M.

[AlexChen]

Hi,

You can use IF condition in Advanced Editor.

I assume your table looks like below.

You can use code below in Advanced Editor to get the records you mentioned.

let

    Source = Table.SelectRows(#"yearInfo", 

             if Date.Month(DateTime.LocalNow()) = 11 and Date.Day(DateTime.LocalNow()) <= 15 then #"yearInfo" else each([relative year] <> -2))

in

Source

This is the result:

To know more details about the usage of power query, please take a look at https://msdn.microsoft.com/en-us/library/mt211003.aspx.

Best Regards

Alex

[tjd]

Yes, but it's probably easier to get it started by using the Create Custom column dialogue and then cleaning it up in Advanced Editor: https://social.technet.microsoft.com/Forums/en-US/c94378ec-98ac-42c6-8a92-5f247b9e3b25/if-function-i...