cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blytonpereira Regular Visitor
Regular Visitor

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

Accepted Solutions
AlexisOlson Member
Member

Re: Powerquery Dynamic LOOKUP value

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

Re: Powerquery Dynamic LOOKUP value

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.

blytonpereira Regular Visitor
Regular Visitor

Re: Powerquery Dynamic LOOKUP value

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

AlexisOlson Member
Member

Re: Powerquery Dynamic LOOKUP value

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.

blytonpereira Regular Visitor
Regular Visitor

Re: Powerquery Dynamic LOOKUP value

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

 

AlexisOlson Member
Member

Re: Powerquery Dynamic LOOKUP value

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

blytonpereira Regular Visitor
Regular Visitor

Re: Powerquery Dynamic LOOKUP value

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 221 members 2,387 guests
Please welcome our newest community members: