Search code examples
powerquerym

Power Query Filter with null (blank) values

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

Tables

Rawdata

Rawdata

Solution

  • 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.