Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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
Told me : "We cannot convert a value of type Table to type Logical".
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.
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.