Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nchinbong
Frequent Visitor

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

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.

2 REPLIES 2
AlexChen
Employee
Employee

Hi,

 

You can use IF condition in Advanced Editor.

 

I assume your table looks like below.

 

1.png

 

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:

 

2.png

 

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
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.