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 trad...
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"
dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity 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