Forum Discussion
tatmaninov
2 years agoFrequent Visitor
Merge - How to treat null value as All
I would like to merge my Flags table with my Data table matching across a number of columns (example below shows 3 matching criteria (Category, Project and SubProject). The only difference to a traditional merge is that if in the Flag table the field is blank/null then this should be treated as wildcard for all, or just ignored.
Flag Table
| Category | Project | SubProject | Flag |
| Alpha | X | 1 | Green |
| Alpha | X | 2 | Green |
| Alpha | Y | 1 | Amber |
| Beta | Z | Red | |
| Charlie |
Data Table
| Category | Project | SubProject |
| Alpha | X | 1 |
| Alpha | X | 2 |
| Alpha | Y | 1 |
| Beta | Z | 1 |
| Beta | Z | 2 |
| Charlie | W | 1 |
| Charlie | W | 2 |
| Charlie | W | 3 |
| Delta | V | 1
|
Desired Output
| Category | Project | SubProject | Flag |
| Alpha | X | 1 | Green |
| Alpha | X | 2 | Green |
| Alpha | Y | 1 | Amber |
| Alpha | Y | 2 | |
| Beta | Z | 1 | Red |
| Beta | Z | 2 | Red |
| Charlie | W | 1 | Green |
| Charlie | W | 2 | Green |
| Charlie | V | 3 | Green |
| Delta | U | 1 |
|
Currently I try like this, but it does not return expected result, any guidance would be much appreciated.
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Project", type text}, {"SubProject", Int64.Type}}),
Buffer=Table.Buffer(Flags),
#"Added Custom" = Table.AddColumn(#"Changed Type","Flag",(i)=>Table.SelectRows(Buffer, each
(i[Category] = [Category] or i[Category] = null) and (i[Project] = [Project] or i[Project] = null) and (i[SubProject] = [SubProject] or i[SubProject] = null)
) [Flag]),
#"Expanded data" = Table.ExpandListColumn(#"Added Custom", "Flag")
in
#"Expanded data"
I intend to apply this to tables of data with circa 100K rows, so if this is also not the most efficient approach I am open to completey different approaches.