Filter Data

[RsimonAlign]

Hi ,

I Have an sql server query and i want to reduce the amount of data by filter the data with list from excel before it loaded to power bi.

For Example :

SQL 

Select * From Customers (Returns 10000 Rows)

Excel

Customer ID 

100

200

I want to filter the sql query in the where clause so it can filter and return just the customers in the Excel table.

The Result Need to be just rows of customers 100,200

TNX

[mahoneypat · Accepted solution]

Ok.  Please try my approach.  If it is too slow to refresh, you could also try to "fold" your Excel values into your Filter step of your database query, as follows:

1. Bring in your Excel data

2. Right click on your customers column and choose "Drill Down".  This will convert it to a List of just the values in that column. Call the query "CustomerList".

3.  Bring in your database data, and make the first step a Filter step.  Choose any few values of customer (you will replace in next step, you just want it to make most of the statement for you).

4.  Replace the "each" part in the formula bar with this

Typical Filter step: = Table.SelectRows(#"Previous Step", each ([Customer] = "CustomerA" or [Customer] = "CustomerB"))

Replace With: = Table.SelectRows(#"Previous Step", each List.Contains(CustomerList, [Customer]))

If you do this filter step right away in your database query, it may "fold" back to the server and have the filtering occur there.  Note that you will need to adjust your Privacy settting potentially for this to work (since it requires sending your Customer IDs to your database (which should be fine).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[v-alq-msft]

Hi, @RsimonAlign 

Based on your description, I created data to reproduce your scenario and tested with Sql Server data source.

Test:

When you connect to Sql Server, you may input the sql statement like below to filter the data.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RsimonAlign]

Tnx

It's work.

but i needed to change the type of the firlds to text.

[mahoneypat]

If you are just trying to reduce the final amount of data in your model (vs. improve refresh performance), you can use this approach.

1. Bring in your database data in one query

2. Bring in your excel table in a second query (disable load)

3. On the first query, merge in your excel data query on the Customer column, using an inner join

4. Don't expand the table. Just remove the new column that has the table.

This uses the merge step to filter only.  You will end up with just the database rows for the customers listed in the Excel file.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[RsimonAlign]

Hi,

I don't only reduce data.

I want to filter the data before id loaded to power bi.

My Fact table include 4 milions rows and i want to load just few of them that are found in the excel customers table.

[mahoneypat · Accepted solution]

Ok.  Please try my approach.  If it is too slow to refresh, you could also try to "fold" your Excel values into your Filter step of your database query, as follows:

1. Bring in your Excel data

2. Right click on your customers column and choose "Drill Down".  This will convert it to a List of just the values in that column. Call the query "CustomerList".

3.  Bring in your database data, and make the first step a Filter step.  Choose any few values of customer (you will replace in next step, you just want it to make most of the statement for you).

4.  Replace the "each" part in the formula bar with this

Typical Filter step: = Table.SelectRows(#"Previous Step", each ([Customer] = "CustomerA" or [Customer] = "CustomerB"))

Replace With: = Table.SelectRows(#"Previous Step", each List.Contains(CustomerList, [Customer]))

If you do this filter step right away in your database query, it may "fold" back to the server and have the filtering occur there.  Note that you will need to adjust your Privacy settting potentially for this to work (since it requires sending your Customer IDs to your database (which should be fine).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[RsimonAlign]

Tnx

This solution filter the data after is loaded.

I need a solution to filter the data before it loaded.

i need to apply filter in the where statment.

something like :

Select * From customers where CustomerID IN ( List ).

Do you know what is the syntax to do it ?

[mahoneypat · Accepted solution]

That is what query folding is.  It filters the data on the server before loading it into Power BI.  Please give my suggested approach a try.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat