Power Query Filter with null (blank) values

Question

I have a problem with Power query and filterting based on custom excel cell parameters

Right now the code works fine as long there is a value in every cell in excel

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"INC", Int64.Type}, {"Datum", type datetime}, {"Service", type text}, {"Owninggroup", type text}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each [Datum] > Tabelle3),
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Gefilterte Zeilen", each Text.StartsWith([Service], Tabelle4)),
    #"Gefilterte Zeilen2" = Table.SelectRows(#"Gefilterte Zeilen1", each Text.Contains([Owninggroup], Tabelle5))
in
    #"Gefilterte Zeilen2"

Now if there is a blank value in any of the tables it doesnt work

The goal is that when there is a null value it should just do the filter before that and my code looks like this:

= Table.SelectRows(#"Gefilterte Zeilen1", each if (Text.Contains([Owninggroup], Tabelle5)) = null then #"Gefilterte Zeilen1" else Text.Contains([Owninggroup], Tabelle5))

Currently I get the error that null cannot be converted to text but Im not sure why.

Any help how to implement this would help tremendously

EDIT: for clarification of what each table entails and the rawdata it draws from

Table3 Table4 and Table5

Rawdata

What is in Tabelle3, Tabelle4 and Tabelle5? And what sholud be the result of each [Datum] > Tabelle3? — Storax
@Storax I edited the main post with a picture to show from left to right. I hope this clarifies it a bit better. The first one with tabelle3 basically should just filter after the date — Alexander R.

Storax Storax · Accepted Answer · 2021-02-06T14:06:35

As you did not show how you did load Tabelle3 to Tabelle5 I guess you did a drilldown to the single values. In this case you just check if the value is null and if it is you just do not filter. Code would look like that

#"Gefilterte Zeilen" = if Tabelle3 is null then #"Geänderter Typ" else Table.SelectRows(#"Geänderter Typ", each [Datum] > Tabelle3),

The complete code would be like that

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"INC", Int64.Type}, {"Datum", type datetime}, {"Service", type text}, {"Owninggroup", type text}}),
    #"Gefilterte Zeilen" = if Tabelle3 is null then #"Geänderter Typ" else Table.SelectRows(#"Geänderter Typ", each [Datum] > Tabelle3),
    #"Gefilterte Zeilen1" = if Tabelle4 is null then #"Gefilterte Zeilen" else  Table.SelectRows(#"Gefilterte Zeilen", each Text.StartsWith([Service], Tabelle4)),
    #"Gefilterte Zeilen2" = if Tabelle5 is null then #"Gefilterte Zeilen1" else Table.SelectRows(#"Gefilterte Zeilen1", each Text.Contains([Owninggroup], Tabelle5))
in
    #"Gefilterte Zeilen2"

Your approach is valid but unusual. Please have a look at Building a parametertable how one could do it in a different way.

Power Query Filter with null (blank) values

1 Answers