Forum Discussion
Power Query Add Custom Column with multiple criteria lookup from other table
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] ) )
You'll usually get better performance with a join in this kind of scenario. In case you somehow added a dependency on 'Finance Book' within 'AC_CC Mapping', I've written this as a third query, which should avoid circular ref errors.
// Assuming that we'll rename original 'Finance Book' as 'Finance Book_init' and disable load
// We can then rename this new query as 'Finance Book' to load into model
let
// Get dates from start - end on map and expand
Map = #"AC_CC Mapping",
Map_AddDates = Table.AddColumn(
Map, "Dates",
each List.Dates(
[Start Date],
Int64.From( [End Date] - [Start Date] ) + 1,
#duration(1,0,0,0)
),
type {date}
),
Map_ExpandDates = Table.ExpandListColumn(Map_AddDates, "Dates"),
// join map and expand Contract Year on above Map_ExpandDates
Book = #"Finance Book_init",
Book_JoinMap = Table.NestedJoin(
Book, {"Cost Center", "Posting Date"},
Map_ExpandDates, {"Cost Center", "Dates"},
"join", JoinKind.LeftOuter
),
Book_ExpandContractYear = Table.ExpandTableColumn(
Book_JoinMap, "join",
{"Contract Year"}, {"Contract Year"}
)
in
Book_ExpandContractYear
----------
As an aside, a circular reference error from the AmiraBedh's solution seems odd. If there was a circular reference issue at the time of your original post, I believe it would have taken priority over your type mismatch error (as in, you would not have seen the error you posted, only the circular ref error). Did you change something in your queries (that would have introduced a Finance Book -> AC_CC Mapping dependency) between your original post and when you tried solutions? Or did you paste the code (which references FinanceBook) inside the FinanceBook query? If a query references itself, that would also cause the error.
Thank you MarkLaf. Even with your solution I am getting the Cyclic error. There was not a cyclic error occurring at the time of my intitial post. Originally, my FinanceBook table had a calculated column in it that referenced the Account mapping table. This occurred after the transform process. However, when I received the cyclic reference after applying AmiraBedh's solution, I removed the tables completly from the file then re-added them without any transformations, custom columns etc to ensure clean, un-changed, data.
I did follow-these steps with your code:
Open Power Query -> Select FinanceBook -> Add Column -> Customer Column -> Paste code -> Ok.. result: Cyclic error.
Table 1 is imported using a SQL query where as Table 2 is an excel spreadsheet. I have no doubt I am doing something wrong here and truly appreciate yours and everyones help on this. Ive been thrown in the fire a little green.