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 everyone,
I would like ot make an IF statement based ona vLOOKUP in Powerquery.
For example I have the followingcolumns:
Date Financial Month Number
01/01/2018 1
02/01/2018 1
03/01/2018 2
Each value in the Date column is unique. I would like to make a vlookup formulas such as to RETURN A SINGLE value of the FINANCIAL MONTH NUMBER when looking up TODAYS date in the DATE column.
e..g if Todays date is 03/01/2018 then the results would be 2.
Another option instead of porviding a single result would be to duplicate this single result in a new column.
Thanks
Blyton
Solved! Go to Solution.
The previous step name is usually of the form #"Step Name". You have to use the octothorpe and quotes unless you renamed that step in the advanced editor. I'm guessing this is what you want:
= List.Single( Table.SelectRows( #"Fin YearMonthM", each [Date] = CurrentDate )[Fin MonthNo] )
You should be able to use something like this to look up the Financial Month Number:
List.Single( Table.SelectRows( DateTable, each [Date] = DateTime.Date(DateTime.LocalNow()) )[Financial Month Number] )
What this does is select all the rows in your DateTable that have Date matching today's date and then takes the Financial Month Number column from that filtered table, which is then just a list. Finally, List.Single extracts that single value in the list.
Hi @AlexisOlson
Thank you for your reply
I havefollowed your method
= List.Single( Table.SelectRows( FinCalendar, each [Date] = CurrentDate )[Fin MonthNo] )
"FinCalendar" is the name of my calendar table. However I am receiving an error
"Expression.Error: A cyclic reference was encountered during evaluation."
I have shared my calendar table in the following link via GoogleDrive
https://drive.google.com/open?id=1QjwGPWnXa4WyJJaOlO7YXuasXVQMYMKj
The name of the step is Custom1
Thanks in advance
Are you trying to add this column to your date table, FinCalendar? If so, then, of course, you get a circular reference since your referencing the query within Table.SelectRows and you should try replacing FinCalendar with the name of the previous step in the query instead.
Yes I am trying to add it directly to my table FinCalendar.
I tried replacing FinCalendar with the name of my previous step and get the folowing error
Expression.SyntaxError: Token Comma expected.
= List.Single( Table.SelectRows( Fin YearMonthM, each [Date] = CurrentDate )[Fin MonthNo] )
The previous step name is usually of the form #"Step Name". You have to use the octothorpe and quotes unless you renamed that step in the advanced editor. I'm guessing this is what you want:
= List.Single( Table.SelectRows( #"Fin YearMonthM", each [Date] = CurrentDate )[Fin MonthNo] )
Thank you it worked perfectly.
The result is a single value that shows up.
I inserted a step after this and I would like to make a simple IF statement comparing this extracted value vs the Month from the CurrentDate
statement something like
if [Vlookup to Fin MonthNo] = Date.Month(CurrentDate) then "xx" else "xy"
Vlookup to Fin MonthNo is the value I calculated earlier using your code.
However I get an error The field 'Vlookup to Fin MonthNo' of the record wasn't found. I am assuming the problem is that the value we obtained "Vlookup to Fin MonthNo", M code is not recognizing it as a field. Is there any way to tell M to recognice as a field. Or maybe an alternative is to make a new column and fill this column with the result of the calcualtion from the previous code ....and then this would be a field and hence we can do a if statement relating to this newly calculated field ?
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |