Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
101 | |
72 | |
47 | |
39 | |
33 |
User | Count |
---|---|
158 | |
102 | |
60 | |
43 | |
40 |