Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
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...
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |