cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkJames Resolver I
Resolver I

Pass List Value through SQL Statement without Parameter

Hi,

 

I am using a parameter to filter a table in Query Editor - the parameter is filtering the table based on an account name. This table will return one single row of information.

 

From this single row I want to take one of the column, ID, and pass it through a SQL query, so they query only returns information for that account ID. I don't want to use a parameter on this query as I would need to pull in all the information first, which would be a massive amount of data.

 

I'm able to create a list from this filtered table that shows only one ID, which will change with the account parameter.

 

Can someone tell me how I can use this single list value and pass it through my SQL query? Using a parameter doesn't work as I'll need to select the first parameter, apply it, then choose the second parameter.

 

Any suggestions?

2 ACCEPTED SOLUTIONS

Accepted Solutions
MarkJames Resolver I
Resolver I

Re: Pass List Value through SQL Statement without Parameter

Hi @v-frfei-msft,

 

I found this description which was worked for me:

 

https://www.excelguru.ca/blog/2016/04/28/pass-parameters-to-sql-queries/

 

I needed to add in a couple of other fields and do some moving around but it's working great now.

 

Thanks for the help.

View solution in original post

MarkJames Resolver I
Resolver I

Re: Pass List Value through SQL Statement without Parameter

Hi @Romain_FOURNIER ,

 

I figured out a way around it - I created a separate Day, Month and Year filter which I then passed through my SQL statement as numbers to make the date field. I've detailed what I did in this other thread:

 

https://community.powerbi.com/t5/Service/Passing-a-Date-Parameter-Through-SQL-Statement-Refreshing-i...

 

Thanks,

MarkJames

View solution in original post

5 REPLIES 5
Community Support
Community Support

Re: Pass List Value through SQL Statement without Parameter

Hi @MarkJames,

 

I make an example for your reference.

 

1.Import an excel file to desktop add a custom column based on id column then create the parameter in power query.

 

1.png

 

 

M code in the power query is like this for step1.

 

(para as text) as table =>
let
    Source = Excel.Workbook(File.Contents("D:\Case\20180810\New Microsoft Excel Worksheet.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "account name"}, {"Column2", "id"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([account name] = para)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each "'" &[id] &"'")
in
#"Added Custom"

 

2.Then we can add some steps in the Advanced editor.

 

(para as text) as table =>
let
    Source = Excel.Workbook(File.Contents("D:\Case\20180810\New Microsoft Excel Worksheet.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "account name"}, {"Column2", "id"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([account name] = para)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each "'" &[id] &"'"),
    keylist= Text.Combine(#"Added Custom"[Custom],","),
    select1="SELECT * FROM servername.databasename.dbo.tablename WHERE  id IN (" & keylist & ")",
    Source1 = Sql.Database("servername ", "databasename", [Query=select1])
in
    Source1

 

3.Then we can get the excepted result once we invoke the parameter.

 

2.png

 

If you still have questions, please share sample data of your table and post SQL query here.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
MarkJames Resolver I
Resolver I

Re: Pass List Value through SQL Statement without Parameter

Hi @v-frfei-msft,

 

I found this description which was worked for me:

 

https://www.excelguru.ca/blog/2016/04/28/pass-parameters-to-sql-queries/

 

I needed to add in a couple of other fields and do some moving around but it's working great now.

 

Thanks for the help.

View solution in original post

Romain_FOURNIER
Frequent Visitor

Re: Pass List Value through SQL Statement without Parameter

Let's suppose that you have a table of equities and that you want to build an SQL statement limited to some equities.

1/ In Power Query Editor, create the EquitiesList parameter as text.

2/ Concatenate all of your selected equities in one line : 

To do it, in a word processor or in Excel, you may replace [CRLF] (CarriageReturn/LineFeed) by [', '] : 

FR0011167451
FR0011257914
FR0012034015

'FR0011167451', 'FR0011257914', 'FR0012034015'

3/ Integrate your EquitiesList parameter in your PQL code (~SQL used in PQE via Advanced Editor) : 

IN (#(lf) " & EquitiesList & ")#(lf)

 

 

MarkJames Resolver I
Resolver I

Re: Pass List Value through SQL Statement without Parameter

Hi @Romain_FOURNIER ,

 

I figured out a way around it - I created a separate Day, Month and Year filter which I then passed through my SQL statement as numbers to make the date field. I've detailed what I did in this other thread:

 

https://community.powerbi.com/t5/Service/Passing-a-Date-Parameter-Through-SQL-Statement-Refreshing-i...

 

Thanks,

MarkJames

View solution in original post

jay_rpj
Frequent Visitor

Re: Pass List Value through SQL Statement without Parameter

@Romain_FOURNIER  I am stuck in the same point . I tried IN (#(lf) " & EquitiesList & ")#(lf) and yet getting the We cannot apply operator & to types Text and List.  What is lf and is the usage correct ?  I am trying to pass the value '70030075','70030092','70030086' as parameter to cosmos DB select query . But its giving me the above error

 

and c.orderid IN (#(lf) "&Txf&" #(lf))"

 

 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors