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

        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 ?