Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Power Query heroes!
My organisation has a table as attached. I would like to create a cloumn to filter my reports for the last 3 'admission' years.
I was thinking of something along the lines of if [Is Current Admission Year] = Y then list {[Academic Year], [Year Minus 1], [Year Minus 2]} of that row to every row.
Then a column to check if the academic year is equal to any value in the list, so only 2020,2019,2018 would result in true dynamically.
How can I apply the result of the first if statement to every row?
Thanks in advance for your suggestions.
JK
Solved! Go to Solution.
Hi @Anonymous
Fixing a little bit @artemus' code:
CurrYear = PreviousStep{[Is Current Admissions Year Flag = "Y"]}[Academic Year],
LastThree = Table.AddColumn(PreviousStep, "Is in last 3", each 0 <= (CurrYear - [Academic Year]) and (CurrYear - [Academic Year]) < 3, Logical.Type)
First, computing Current Academic Year only once --> less load
Second, calling that to create a true/false column.
I wasn't sure if you want current year included or not. If not, you should change the constraints.
Then, you can use this T/F column however you like. Since CurrYear is dynamic, it will change when data changes.
Hi @Anonymous
Fixing a little bit @artemus' code:
CurrYear = PreviousStep{[Is Current Admissions Year Flag = "Y"]}[Academic Year],
LastThree = Table.AddColumn(PreviousStep, "Is in last 3", each 0 <= (CurrYear - [Academic Year]) and (CurrYear - [Academic Year]) < 3, Logical.Type)
First, computing Current Academic Year only once --> less load
Second, calling that to create a true/false column.
I wasn't sure if you want current year included or not. If not, you should change the constraints.
Then, you can use this T/F column however you like. Since CurrYear is dynamic, it will change when data changes.
You could do this in query, but it is probably easier with a DAX calculated column with a formula like this, replacing with the Table[Column] for your actual Date column.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for taking ther time to offer a suggestion Pat. Unfortunately this currently needs to be in Power Query for a table as described. I don't have a "date" to call upon in this way.
Best wishes
JK
Assuming that [Is Current Admission Year Flag] is unique in the table you can just grab that row at any time (and there is no future year in the table).
Like:
= PreviousStep{[Is Current Admission Year Flag = "Y"]}[Academic Year] - [Acedemic Year] <= 3
Where PreviousStep is the name of your Previous step (e.g. Source if it is the first step)
Thanks Artemus.
When I try the following:
#"Last three" = Table.AddColumn(#"Changed Type", "Counting Years", each {[Is Current Admissions Year Flag]="Y"}[Academic Year]-[Academic Year]<=3)
There is an error, cannot apply field access to to type list. Am I missing something?
Also you mention "no future years in the table". For which there is. The flag will always be one row up from the bottom if that makes a difference?
Thanks
JK
Opps, you are right, I made a typo:
#"Last three" = Table.AddColumn(#"Changed Type", "Counting Years", each PreviousStep{[Is Current Admissions Year Flag="Y"]}[Academic Year]-[Academic Year]<=3)
Here is what is should be
Syntactically I can get a result but this is True,False.
I basically need to go to the row where [Is Current Admissions Year] = "Y" (will change each year), then get the years required, either by calculation or refering to the coumns that host them, [Academic Year], [Year Minus 1], [Year Minus 2]. Place these specific values in a new column in each row.
From there I can check if the academic year for a row matches any value in the new column to use as a filter later.
Does that make sense?
Sure that is easy, just remove the last part of the formula:
#"Last three" = Table.AddColumn(#"Changed Type", "Counting Years", each PreviousStep{[Is Current Admissions Year Flag="Y"]}
Then do a column expansion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |