CODEWITHSUNDEEP
sqlsql-serverpowerbi
user10099944
user10099944

Reputation:

Power BI NativeQuery - cannot convert from Type to Text

I created SQL stored procedure with 2 parameters: DateFrom and DateTo (both are of type DATE in my sql procedure).

I want to write a native query in Power Query Editor so first I created there 2 parameters for dates (type:date/time) : StartDate and EndDate. Unfortunately, when I'm trying to write a native query, the following error occurs: "can't convert from data type Record to Text". Do you know what's wrong with it?

This is procedure invoke:

enter image description here

and my M code:

let
    Source = Sql.Database("Server\12", "Base1"),

        Query = Value.NativeQuery(
            Source, 
            [Query="EXEC [dbo].[sp_Sales] @DateFrom = '" & Date.ToText(StartDate) & "', @DateTo = '" & Date.ToText(EndDate) & "'"])
  
in 
 Query

I also tried with more " :

let
    Source = Sql.Database("Server\12", "Base1"),

        Query = Value.NativeQuery(
            Source, 
            [Query="EXEC [dbo].[sp_Sales] @DateFrom = """ & Date.ToText(StartDate) & """, @DateTo = """ & Date.ToText(EndDate) & """"])
  
in 
 Query

where is the problem?

Upvotes: 0

Views: 1416

Answers (1)

user10099944
user10099944

Reputation:

I found a solution for it. The key is to use NativeQuery here. The steps are as following:

  1. Create stored procedure with parameters (e.g. DateFrom and DateTo) and import data to Power BI
  2. In PowerQuery Editor create 2 parameters. Let's say startDate and endDate. Set some default values:

enter image description here

  1. Go to 'Advance Editor' to change M code. The proper one should be as following:
let
    Source = Sql.Database("ServerName\12", "Base2"),
    Query = Value.NativeQuery(
            Source, 
            "EXEC [dbo].[storedProcedure_XYZ] @DateFrom = '" & Date.ToText(startDate) & "', @DateTo = '" & Date.ToText(endDate) & "'")
    
in
    
Query

@DateFrom and @DateTo are SQL parameters, PQ parameters (startDate and endDate) must be assigned to them in a way I showed above.

Important thing is that you can't put EXEC.... statement in 'SQL statement field' for importing data (of course you need to fill out server name etc.). I mean you can do it first to import raw data (see 1st point) but after that this field should remain empty. The whole job is now done by your M code using Native Query.

enter image description here

  1. After clicking 'Ok' in your 'Advanced Editor' (see 3rd point) your SQL query will be executed along with parameters. Now you can see data refreshed in Power BI.

  2. Go to visualization layer in your Power BI. Click on this little arrow down for 'Transform Data' and choose 'Edit Parameters':

enter image description here

  1. Now enter values for your parameters.

enter image description here

Click OK and that's it! Your SQL query is being refreshed now based on parameters you passed from PBI Visualization layer and all refreshed data will be loaded into your data model in PBI - that means your visualizations will be updated automatically with new data. It's not statis any longer - it's dynamic now:)

Another good thing about it is that your base table which you imported to PQ from SQL won't change to function (as it usually is when you parametrize queries) - it still remain table even though you use parameters on your base query. It makes less problems with loading data into PBI data model, all columns remain on their place and none of them are removed. It works perfectly for me.

I also turned off Native Query Approval (go to Options>Security) as to PBI doesn't ask you for permission to execute your query. That's good thing to do when you use Native Query (bear in mind security issues, it's important here).

enter image description here

Upvotes: 1

Related Questions