Forum Discussion
JVos
Helper IV
Helper IVConditional join with conditions (=, <>) stored in columns
I want to join records in Table A with records in Table B, based on conditions in Table B. How can this be done the easiest way, probably with a SelectRows statement? Contents of Table A: ID ...
Hi JVos
Try this
let TableA = Excel.CurrentWorkbook(){[Name="tblRoute"]}[Content], TableB = Excel.CurrentWorkbook(){[Name="tblBAAM"]}[Content], TableA_TableB = Table.AddColumn(TableA, "Outcome", each Table.SelectRows(TableB, (Magic) => Magic[Name]=[Name] and (if Magic[CoatingCondition] = null then true else Expression.Evaluate("[Coating]" & Magic[CoatingCondition], [_=_])) and (if Magic[UsageCondition] = null then true else Expression.Evaluate("[Usage]" & Magic[UsageCondition], [_=_]))) ), Result = Table.ExpandTableColumn(TableA_TableB, "Outcome", {"Outcome"}), #"Removed Columns" = Table.RemoveColumns(Result,{"Coating", "Usage"}) in #"Removed Columns"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AlB
Community Champion
Community ChampionHi JVos
Try this
let
TableA = Excel.CurrentWorkbook(){[Name="tblRoute"]}[Content],
TableB = Excel.CurrentWorkbook(){[Name="tblBAAM"]}[Content],
TableA_TableB = Table.AddColumn(TableA, "Outcome", each Table.SelectRows(TableB,
(Magic) => Magic[Name]=[Name] and (if Magic[CoatingCondition] = null then true else Expression.Evaluate("[Coating]" & Magic[CoatingCondition], [_=_]))
and (if Magic[UsageCondition] = null then true else Expression.Evaluate("[Usage]" & Magic[UsageCondition], [_=_])))
),
Result = Table.ExpandTableColumn(TableA_TableB, "Outcome", {"Outcome"}),
#"Removed Columns" = Table.RemoveColumns(Result,{"Coating", "Usage"})
in
#"Removed Columns"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
