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

    • blytonpereira's avatar
      blytonpereira
      Helper II
      7 years ago

      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's avatar
        AlexisOlson
        Super User
        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]
)