I am struggling to find some effective way how to utilize join with multiple conditions in my existing process to
To simplify, I have three tables:
EmpTable = with columns Emp.ID, Dept.ID, Profit.Center, Product.Code and YearMonth (nothing is null)
RefTable2022 = mapping table, which is evaluating combination of Dept.ID and Product.Code and giving me Dept.Category
RefTable2023 = mapping table, which is evaluating:
either Dept.ID + Profit.Center (for those rows the Product.Code is null),
or Dept.ID + Product.Code (and then Profit.Center is null)
and both are giving me Dept.Category
Output should be one table, basically EmpTable with added one extra column Dept.Category, as it will continue in the process for next transformations.
And I need some effective way, how to merge EmpTable with RefTable2022 for all records <= 2022, and with RefTable2023 for anything >2023. The YearMonth part is not a problem from time intelligence perspective, more the actual merging design and setup is, especially I dont know what approach to take with 2023 table, and did not found a way how to incorporate either dynamic variable or if statement.
I have already logic with 2022 table in place as it is quite straightforward, but now I am struggling to incorporate also 2023. I was thinking about something like this, but dunno how to handle that key part:
let
Source = MappingTable2023,
AddKeyColumn = Table.AddColumn(
Source,
"Key",
each if [Profit.Center] = null then "Product.Code" else "Profit.Center",
type text),
Merge = Table.NestedJoin(
MappingTable2023, {"Dept.ID", AddKeyColumn},
EmpTable, {"Dept.ID", AddKeyColumn},
"Department Category",
JoinKind.RightOuter)
in
Merge
This is part of very big and complex data preparation process we are using on monthly basis, and I could do this by slice and dicing into small pieces and somehow patch them together, but I would like to find some more elegant way how to handle this. Also, there are milions of rows in EmpTable, so performance is important.
Ideally if I can somehow put together into one query all parts mentioned above, as there is currently about 35 queries in the process and would like to avoid adding unnecessary complexity.
