Forum Discussion

tatmaninov's avatar
tatmaninov
Frequent Visitor
2 years ago
Solved

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

 

CategoryProjectSubProjectFlag
AlphaX1Green
AlphaX2Green
AlphaY1Amber
BetaZ Red
Charlie   

 

Data Table

CategoryProjectSubProject
AlphaX1
AlphaX2
AlphaY1
BetaZ1
BetaZ2
CharlieW1
CharlieW2
CharlieW3
DeltaV1

 

 

Desired Output

CategoryProjectSubProjectFlag
AlphaX1Green
AlphaX2Green
AlphaY1Amber
AlphaY2 
BetaZ1Red
BetaZ2Red
CharlieW1Green
CharlieW2Green
CharlieV3Green
DeltaU1 

 

 

 

 

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.