Forum Discussion
tatmaninov
2 years agoFrequent Visitor
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...
- 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"
tatmaninov
2 years agoFrequent Visitor
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"