How to filter large data sources pre import

[Anonymous]

Hi,

I have a large data source that I would like to filter pre import to Power BI. My data source is an SQL database. I have researched the use of query parameters and how the M-code can be manipulated to place the parameter in my SQL query via the syntax "&Parameter&" to load only a single common value pre import, or a list of values or a query containing a list of values. However, this only allows me to examine the rows matching a single value, much like a drop down list selection, where I actually need to examine the rows from all the values in the drop down list at once, not individually.  I've read that currently Power BI doesn't support multiple-selection right now, is this correct? If so, I am shocked.

Thanks.

[Icey]

Hi @Anonymous ,

Have you tried this?

Best Regards,
Icey

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

[Anonymous]

Yes, this is the first thing I tried. How can I use a parameter in this option to filter my data pre import? Returning everything takes hours to execute. The parameter will need to hold hundreds of dynamic values that will filter the output and I need the output to show all rows, not single rows.

Thanks.

[Anonymous · Accepted solution]

SOLVED MYSELF.

If you have a requirement to filter large data sources (Cube or SQL) pre import to Power BI you will have to build a string containing all your filter values (sourced from a text or excel file if you wish) in the correct format for your type of query. The example code below builds a string of dynamic values from a single column within an excel file to suit both a cube and sql query WHERE IN clause. This string is then injected within the query pre import via a variable reference. Please note, for this to work within the Power BI Service where the source excel file providing the dynamic filter values has been updated, that the dataset will have to be refreshed to inject the latest values into the query for the correct output:

let
Source = Excel.Workbook(File.Contents("<path to excel file>"), null, true),
Values_Sheet = Source{[Item="Values",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Values_Sheet,{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Values"}}),
__Value = #"Renamed Columns"[Value],
DelimitedList__Value = List.Accumulate(__Value,"",
(state,current)=>
if state=""
then state&"'"&current&"'"
else state&",'"&current&"'"
),
ConnQuery = Sql.Database("<SERVER>", "<DB>", [Query="Select * #(lf)From <TABLE> #(lf)where <COLUMN> in (" &DelimitedList__Value& ")"])
in
ConnQuery

Cube (MDX Formatting):

let
Source = Excel.Workbook(File.Contents("<path to excel file>"), null, true),
Values_Sheet = Source{[Item="Values",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Values_Sheet,{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Values"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "ValueCode", each "[Value].[Value Code].&[" &[value_code]&"]"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"value_code"}),
__Value = #"Removed Columns"[ValueCode],
DelimitedList__Value = List.Accumulate(__Value,"",
(state,current)=>
if state=""
then state&""&current&""
else state&","&current&""
),

ConnQuery = AnalysisServices.Database(<SERVER>, <DB>,[Query="<query header>...#(lf) WHERE #(lf)(#(lf)#(lf){"&DelimitedList__Value&"})", Implementation="2.0"])
in
ConnQuery

[jshutters]

Would you provide an example of what you'd like, as I'm not fully understanding your issue? And where do you want the multiple-selection to occur? If you have a parameter with the value set to multiple items e.g. "A, B, C", then you can use the parameter within your Where clause e.g. "Where Product IN (" &Parameter& ") ..." This will return rows that match multiple values. Is this what you're looking for?

[Anonymous]

This is exactly what I'm wanting to do but if I want to return product A, B and C as three rows in the output this is not possible through the parameter, it will only return one of A, B or C at any one time and allow the others to be viewed via a drop down selection. I need it to be a parameter as the products for selection are dynamic. These will be maintained in a spreadsheet that I will use as a lookup. If I return all products within my query the query will take hours to execute thus I need to filter my query pre import to Power BI.

Thanks.