Forum Discussion
Filter Power Query
Hi LOTR_Nerd99 , thanks for the answer - I really appreciate any help!
I think what you are saying is exactly what I have tried to do. In details I have:
1. Created a Power Query with 6 of my 50+ columns, selected all distinct rows of this and loaded this table into Excel.
2. Created 6 slicers, one for each of the columns.
3. Connected the table to Power Query, combined my 6 columns into one and made sure to keep only those rows that was left after filtering in the slicers. Thus I have a list.
4. Created a Power Query - combined the same 6 columns into a new column and then done exactly as you suggest with List.Contains.
But for some reason this works really bad. Do you have any suggestions as why this is the case?
madsb1999, you could increase performance by buffering the distinct list.
BufferedList = List.Buffer(Distinct_List),
#"Filtered to matching list" = Table.SelectRows(Source,
each List.Contains(
BufferedList,
[field_name_in_main_table]
)
)
Also, you could build your distinct list in power query as well:
You will need three tables.
- Main table that will be reference by table 2 and table 3 below.
- Distinct List that reference the main table
- Table to filter that reference the main table
To build the Distinct List, you can use the following code:
let
Source = main_table,
#"Inserted Merged Column" = Table.AddColumn(Source, "key", each
Text.Combine(
{[name],
[address],
[city],
Text.From([zip], "en-US")}, //use Text.From if [zip] is a type number
";"),
type text
),
#"Removed Duplicates" = Table.Distinct(#"Inserted Merged Column", {"key"})
in
#"Removed Duplicates"
Also you could try the Power BI forum: https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services