Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.