One way to do that:
- Join (NestedJoin) the two tables
- Filter the mapping table to return the mapping rows if the Region matches or if it is "ALL"
- Filter that filtered table for the City or All, and return the Results column.
- If the second filtering => no rows, or if Result is not
Available
, then No else Yes
- If the second filtering => no rows, or if Result is not
M Code
let
Source = Excel.CurrentWorkbook(){[Name="Mapping"]}[Content],
mapping = Table.TransformColumnTypes(Source,{
{"Country", type text}, {"Region", type text}, {"City", type text}, {"Results", type text}
}),
map = Table.RenameColumns(mapping, List.Transform(Table.ColumnNames(mapping),each {_,"M." & _})),
Source2 = Excel.CurrentWorkbook(){[Name="Actual"]}[Content],
actual = Table.TransformColumnTypes(Source2,{
{"Order", Int64.Type}, {"Country", type text}, {"Region", type text}, {"City", type text}
}),
join = Table.NestedJoin(actual,"Country",map,"M.Country","Join",JoinKind.LeftOuter),
//Filter the Region for named region or ALL
#"Added Custom" = Table.AddColumn(join, "inRegion", each
let
str = [Region]
in
Table.SelectRows([Join], each [M.Region] = "ALL" or [M.Region] = str)),
//Filter for City or ALL + Available
// If rowcount > 0 then "Yes"
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Results", each
let
str = [City]
in
if(Table.RowCount(
Table.SelectRows([inRegion],
each [M.City] = "ALL"
or [M.City] = str
and [M.Results] = "Available")))
> 0 then "Yes" else "No"),
//remove unneeded columns
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Join", "inRegion"})
in
#"Removed Columns"
`