Show all the data when filter is empty

[ageuffrard]

Hi everyone, 

I created filters (Region, Operator defined by "or" and "and", and Source) where users can choose manually and when I press the actualisation button its shows the filtered data on the right, like this : 

When the Operator filter (in orange in the pic) is empty, I want it to show all the data without filters (got 30 lines in total), but I can't find the way to do it.

Here's my Power Query code : 

let

  // ajout des paramètres de filtre                                                                                                                                                                                                                                       
  filtreRegion = f_Region,
  filtreSource = f_Source,
  filtreOperateur = f_Operateur,

  Source = Excel.CurrentWorkbook(){[Name = "tbl_region_source"]}[Content],
  #"Filtered lines" = Table.SelectRows(
    Source,
    each
      if f_Operateur = "or" then
        (([Région] = f_Region) or ([Source] = f_Source))
      else
        (([Région] = f_Region) and ([Source] = f_Source))
      // else if f_Operateur = "" then
      // Source
  ),
  removingDuplicate = Table.Distinct(#"Filtered lines")
in
  removingDuplicate

Thanks for the help !

Alexandre

[Vijay_A_Verma]

Following should work

let

  // ajout des paramètres de filtre                                                                                                                                                                                                                                       
  filtreRegion = f_Region,
  filtreSource = f_Source,
  filtreOperateur = f_Operateur,

  Source = Excel.CurrentWorkbook(){[Name = "tbl_region_source"]}[Content],
  #"Filtered lines" = Table.SelectRows(
    Source,
    each
      if f_Operateur = "or" then
        [Région] = f_Region or [Source] = f_Source
      else if f_Operateur = "and" then
        [Région] = f_Region and [Source] = f_Source
      else Source
  ),
  removingDuplicate = Table.Distinct(#"Filtered lines")
in
  removingDuplicate

[ageuffrard]

Told me : "We cannot convert a value of type Table to type Logical".

[Vijay_A_Verma · Accepted solution]

Assuming that there is one column which contains non blank values. Let's assume this column is Region (Looks like your Numero client is one such column). Then you can use following code

let

  // ajout des paramètres de filtre                                                                                                                                                                                                                                       
  filtreRegion = f_Region,
  filtreSource = f_Source,
  filtreOperateur = f_Operateur,

  Source = Excel.CurrentWorkbook(){[Name = "tbl_region_source"]}[Content],
  #"Filtered lines" = Table.SelectRows(
    Source,
    each
      if f_Operateur = "or" then
        [Région] = f_Region or [Source] = f_Source
      else if f_Operateur = "and" then
        [Région] = f_Region and [Source] = f_Source
      else [Région]<>""
  ),
  removingDuplicate = Table.Distinct(#"Filtered lines")
in
  removingDuplicate

Now' let's assume that there is no such column. In this case, you can insert one Index column. Index column is always non blank and replace [Région]<>"" with [Index]<>""

Then you can remove the Index column after this step.

[ageuffrard]

It works perfectly with the [Région]<>"" method.

And I understand your approach concerning the no-column case, I'll keep that in mind in case it happens.

Thank you very much for your time, your services and the tip. Please have a nice day.