Sourcing data from SQL using Power BI lists

[vikrantarora25]

Hi Folks,

I have to source data from a SQL DB in Power BI and am using where clause in my query. I have created a Power BI list which has hundreds of IDs (integer type) and when I use text.combine M function to combine the list values & separate them by a comma I run into datatype issue. Does anyone know what query to use make a list of integers comma separated and use in where clause.

Expression.Error: We cannot convert the value 2382874 to type Text.
Details:
Value=2382874
Type=[Type]

> 2382874 is the first ID in my list.

here is the M code:

let
Source = Sql.Database("server", "db", [Query="SELECT #(lf) [ID]#(lf) ,[company]#(lf) ,[UserID]#(lf)#(tab)
FROM [dbo].[mysqldb] WHERE [ID] IN (" & Text.Combine(ID_List, ",") & ")" ] )
in
Source

I was suggested by someone to convert type of the list to Text before you try to combine the list values, which i did but no luck. Any suggestions would mean a lot.

Thanks

[v-henryk-mstf · Accepted solution]

Hi @vikrantarora25 ,

According to your needs, you should want to determine whether the [id] column of sql is in this ID list. The role of Text.Combine is to return the result of combining a list of text values into a single text value. You should create query parameters in power query and replace them in the advanced editor. I did a similar test, which can be used as a reference:

related document link:

Text.Combine - PowerQuery M | Microsoft Docs

Pass parameter to SQL Queries statement using Power BI | MVP: Microsoft Azure | Channel 9 (msdn.com)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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