Forum Discussion

blytonpereira's avatar
blytonpereira
Helper II
7 years ago
Solved

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

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    7 years ago

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

6 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    7 years ago

    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's avatar
      blytonpereira
      Helper II
      7 years ago

      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's avatar
        AlexisOlson
        Super User
        7 years ago

        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.