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