Forum Discussion

adentler's avatar
adentler
Advocate I
11 months ago
Solved

Power Query Add Custom Column with multiple criteria lookup from other table

  Hello, I am trying to use Power Query to add a column to a table during the transform process. I need to lookup multiple values from Table 1 (Finance Book) in Table 2 (AC_CC Mapping), and return t...
Need Help
  • adentler's avatar
    adentler
    11 months ago

    All - I believe I was able to determine the solution thanks to all of your inputs. I also found the cyclical reference. It was a calculated column post-transform on the Finance Book table.

     

    Ultimately, I was able to use the following code and the column was added as needed:

     

    = Table.AddColumn(#"Changed Type", "Contract Year", (F) => 
  Table.SelectRows( 
    #"ACC_CC Mapping", 
    (C) => C[Start Date] <= F[posting_date] and C[End Date] >= F[posting_date] 
  )
)

     

     

AmiraBedh's avatar
AmiraBedh
Super User
11 months ago

Hello !

Thank you for posting on Microsoft Fabric community.

The AND only works on logical values but I can see that in your code you are mixing a value ofCost Center with boolean.  I shared the file with the solution.

 

= let
    FB  = Table.TransformColumnTypes(FinanceBook,     {{"Cost Center", type text}, {"Posting Date", type date}}),
    MAP = Table.TransformColumnTypes(#"AC_CC Mapping",{{"Cost Center", type text}, {"Start Date", type date}, {"End Date", type date}}),

    WithCY = Table.AddColumn(
        FB, 
        "Contract Year", 
        (row as record) as nullable text =>
            let
                matches = Table.SelectRows(
                    MAP, 
                    each [Cost Center] = row[Cost Center] 
                      and row[Posting Date] >= [Start Date] 
                      and row[Posting Date] <= [End Date]
                ),
                best = if Table.IsEmpty(matches) 
                       then null 
                       else Table.Sort(matches, {{"Start Date", Order.Descending}}){0}[Contract Year]
            in
                best,
        type nullable text
    )
in
    WithCY

 

 

adentler's avatar
adentler
Advocate I
11 months ago

Thank you! I truly appreciate your response and can definitely see the what you did in the code. However, I am still getting an error, but a different one.. this time the error is: 

 

Expression.Error: A cyclic reference was encountered during evaluation.

 

I used your exact code with great hope! I am pretty new to PBI.. and Power Query so thank you for helping me work through it.