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
blytonpereira
Helper II
Helper II

Powerquery Dynamic LOOKUP value

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

 

1 ACCEPTED 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]
)

View solution in original post

6 REPLIES 6
AlexisOlson
Super User
Super User

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 ?

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.