Home

Using Excel Column for Dynamic SQL Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-655644%22%20slang%3D%22en-US%22%3EUsing%20Excel%20Column%20for%20Dynamic%20SQL%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-655644%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20difficulty%20using%20an%20existing%20excel%20column%20to%20create%20a%20dynamic%20SQL%20power%20query.%20In%20the%20below%20link%20there%20are%20two%20example%20to%20make%20this%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FBI-and-Data-Analysis%2FCreate-dynamic-Power-Query-SQL-by-using-Excel-table-column%2Ftd-p%2F211504%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FBI-and-Data-Analysis%2FCreate-dynamic-Power-Query-SQL-by-using-Excel-table-column%2Ftd-p%2F211504%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20still%20receiving%20an%20error%20after%20following%20the%20links%20instructions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20do%20this%20using%20the%26nbsp%3BValue.NativeQuery%20route%20and%20am%20receiving%20the%20following%20conversion%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EDataSource.Error%3A%20Microsoft%20SQL%3A%20Conversion%20failed%20when%20converting%20the%20nvarchar%20value%20'('MyDataStringHere)'%20to%20data%20type%20int.%3CBR%20%2F%3EDetails%3A%3CBR%20%2F%3EDataSourceKind%3DSQL%3CBR%20%2F%3EDataSourcePath%3Dsysqlwh1%3BPPI%3CBR%20%2F%3EMessage%3DConversion%20failed%20when%20converting%20the%20nvarchar%20value%20'('MyDataStringHere')'%20to%20data%20type%20int.%3CBR%20%2F%3ENumber%3D245%3CBR%20%2F%3EClass%3D16%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20column%20I%20am%20using%20the%20parameter%20against%20is%20data%20type%3A%20int.%20I%20am%20not%20sure%20if%20this%20is%20the%20cause%20of%20the%20error.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20WHERE%20clause%20within%20the%20NativeQuery%20looks%20like%20this%3A%3C%2FP%3E%3CPRE%3EWHERE%20(T3.INTCOLUMN_1%20%3D%201)%20AND%20(%20T3.INTCOLUMN_2%20IN%20(%40Parameter)%20)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20manually%20run%20this%20query%20in%20SSMS%20with%20my%20copied%20parameter%20I%20have%20no%20issues%20with%20the%20data%20type%20as%20either%20an%20int%20value%20or%20a%20text%20value%20in%20the%20WHERE%20clause.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20anyone%20know%20how%20to%20resolve%20this%20error%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E-Jonathan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-655644%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERequests%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657081%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Excel%20Column%20for%20Dynamic%20SQL%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657081%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345295%22%20target%3D%22_blank%22%3E%40JonathanLewis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Jonathan%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20such%20case%20I'd%20use%20something%20like%3C%2FP%3E%0A%3CPRE%3EMyParameters%20%3D%20%221%2C2%22%2C%0ASource%20%3D%20Sql.Database(%22MyServer%22%2C%20%22MyDatabase%22)%2C%0A%20%20%20%20Query%20%3D%20Value.NativeQuery(Source%2C%22%0A--%20here%20is%20SQL%20body%3CBR%20%2F%3EWHERE%20(T3.INTCOLUMN_1%20%3D%201)%20AND%20(%20T3.INTCOLUMN_2%20IN%20(%22%20%26amp%3B%20MyParameters%20%26amp%3B%20%22)%20)%3CBR%20%2F%3E%22)%20%3C%2FPRE%3E%0A%3CP%3EWith%20parameter%20you%20pass%20into%20the%20query%20string%20as%20%22'1%2C2'%22.%20Perhaps%20it%20could%20be%20split%20somehow%2C%20not%20sure.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657101%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Excel%20Column%20for%20Dynamic%20SQL%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657101%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345295%22%20target%3D%22_blank%22%3E%40JonathanLewis%3C%2FA%3E%26nbsp%3B%2C%20by%20the%20way%2C%20if%20your%20parameters%20are%20in%20supporting%20table%20within%20Excel%2C%20let%20say%20tblParam%20with%20the%20column%20Options%2C%20you%20may%20query%20your%20table%2C%20change%20Options%20to%20text%20type%20if%20necessary%2C%20and%3C%2FP%3E%0A%3CPRE%3EMyParameters%20%3D%20Text.Combine(tblParam%5BOptions%5D%2C%20%22%2C%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
JonathanLewis
New Contributor

I am having difficulty using an existing excel column to create a dynamic SQL power query. In the below link there are two example to make this work.

 

https://techcommunity.microsoft.com/t5/BI-and-Data-Analysis/Create-dynamic-Power-Query-SQL-by-using-...

 

I am still receiving an error after following the links instructions.

 

I am trying to do this using the Value.NativeQuery route and am receiving the following conversion error.

 

DataSource.Error: Microsoft SQL: Conversion failed when converting the nvarchar value '('MyDataStringHere)' to data type int.
Details:
DataSourceKind=SQL
DataSourcePath=sysqlwh1;PPI
Message=Conversion failed when converting the nvarchar value '('MyDataStringHere')' to data type int.
Number=245
Class=16

 

The column I am using the parameter against is data type: int. I am not sure if this is the cause of the error. 

 

My WHERE clause within the NativeQuery looks like this:

WHERE (T3.INTCOLUMN_1 = 1) AND ( T3.INTCOLUMN_2 IN (@Parameter) )

 

When I manually run this query in SSMS with my copied parameter I have no issues with the data type as either an int value or a text value in the WHERE clause.

 

Do anyone know how to resolve this error?

 

Thanks in advance!

-Jonathan

2 Replies

@JonathanLewis 

 

Hi Jonathan,

 

In such case I'd use something like

MyParameters = "1,2",
Source = Sql.Database("MyServer", "MyDatabase"),
    Query = Value.NativeQuery(Source,"
-- here is SQL body
WHERE (T3.INTCOLUMN_1 = 1) AND ( T3.INTCOLUMN_2 IN (" & MyParameters & ") )
")

With parameter you pass into the query string as "'1,2'". Perhaps it could be split somehow, not sure.

 

@JonathanLewis , by the way, if your parameters are in supporting table within Excel, let say tblParam with the column Options, you may query your table, change Options to text type if necessary, and

MyParameters = Text.Combine(tblParam[Options], ",")
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies