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...
  • tatmaninov's avatar
    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"