Forum Discussion

ageuffrard's avatar
ageuffrard
Frequent Visitor
4 years ago
Solved

Show all the data when filter is empty

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

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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.

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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's avatar
      ageuffrard
      Frequent Visitor
      4 years ago

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

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        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.