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.

Need Help
  • tatmaninov's avatar
    tatmaninov
    2 years ago

    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"

2 Replies

  • tatmaninov's avatar
    tatmaninov
    Frequent Visitor
    2 years ago

    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"
  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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