Merge - How to treat null value as All

[tatmaninov]

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.

[dufoq3]

Hi @tatmaninov, there is no logic to get Green flag for Charlie...

This should be correct logic (I would say). I understand that you can return flag for the project, if there is no subproject but how you can return flag for subproject when Charlie has different project. For me - it doesn't make sense. Of cource it is possible i.e. the way you did it, but...

Result

let
    FlagTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcswpyEhU0lGKAGJDIHYvSk3NU4rVQZUxwioTCdXjmJuUWgSWcUotAUlEAbECEAelpoCFnTMSi3IyU6GiIBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Project = _t, SubProject = _t, Flag = _t]),
    DataTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcswpyEhU0lGKAGJDpVgdVBEjFJFIuBqn1BKQQBQ2AYge54zEopzMVCA/HK4IVQybOmOwmEtqDti0MIjOWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Project = _t, SubProject = _t]),
    MergedQueries_Cat_Proj_SubProj = Table.NestedJoin(DataTable, {"Category", "Project", "SubProject"}, FlagTable, {"Category", "Project", "SubProject"}, "Cat_Proj_SubProj", JoinKind.LeftOuter),
    MergedQueries_Cat_Proj = Table.NestedJoin(MergedQueries_Cat_Proj_SubProj, {"Category", "Project"}, FlagTable, {"Category", "Project"}, "Cat_Proj", JoinKind.LeftOuter),
    MergedQueries_Cat = Table.NestedJoin(MergedQueries_Cat_Proj, {"Category"}, FlagTable, {"Category"}, "Cat", JoinKind.LeftOuter),
    Ad_TableSelect = Table.AddColumn(MergedQueries_Cat, "TableSelect", each if Table.RowCount([Cat_Proj_SubProj]) > 0 
then [Cat_Proj_SubProj] else 
if Table.RowCount([Cat_Proj]) > 0 
then [Cat_Proj] else [Cat], type table),
    #"Removed Columns" = Table.RemoveColumns(Ad_TableSelect,{"Cat_Proj_SubProj", "Cat_Proj", "Cat"}),
    ExpandedTableSelect = Table.ExpandTableColumn(#"Removed Columns", "TableSelect", {"Flag"}, {"Flag"}),
    ReplacedValue = Table.ReplaceValue(ExpandedTableSelect,"",null,Replacer.ReplaceValue,{"Flag"})
in
    ReplacedValue

[tatmaninov · Accepted solution]

After some experimenting seems I was pretty close, seems I had my logic wrong way round, not sure I unerstand it yet, but this is returning expected results

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Project", type text}, {"SubProject", type text}}),
    Buffer=Table.Buffer(Flags),
    #"Added Custom" = Table.AddColumn(#"Changed Type","Flag",(i)=>Table.SelectRows(Buffer, each

    ([Category] = i[Category] or [Category] = null) and ([Project] = i[Project] or [Project] = null) and ([SubProject] = i[SubProject] or [SubProject] = null)
    
    ) [Flag]),
    #"Expanded data" = Table.ExpandListColumn(#"Added Custom", "Flag")
in
    #"Expanded data"