Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply

Sourcing data from SQL using Power BI lists

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

 

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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:

vhenrykmstf_0-1624528133923.png

vhenrykmstf_1-1624528147668.pngvhenrykmstf_2-1624528158571.png

vhenrykmstf_3-1624528171602.png

 

 

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.

View solution in original post

1 REPLY 1
v-henryk-mstf
Community Support
Community Support

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:

vhenrykmstf_0-1624528133923.png

vhenrykmstf_1-1624528147668.pngvhenrykmstf_2-1624528158571.png

vhenrykmstf_3-1624528171602.png

 

 

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.