Changing from Stored Procedure based Source with Import Data to a Table/View

[mnawaz]

I am having issues when trying to change the Source from a Stored Procedure based to a Table or a view. I don't get the navigation page to select the table/view after changing the source and removing the Stored Procedure from the SQL Statement Box.

[TomMartens · Accepted solution]

Hey,

this is not as simple as one might want it to be, but nevertheless it's possible with a little adjusting of the generated M code using the Advanced Editor in Power Query.

The following shows the M code from the Advanced Editor, the query is using a stored procedure to return some data:

let
    Source = Sql.Database("mycomputername\sqlserver2017", "wideworldimportersdw", [Query="execute dbo.a_simple_select"])
in
    Source

Another important thing to notice is that there is no Navigation step.

Now if you create another connection to the same database and now you use the object navigation (meaning selecting one of the available tables or views, the M code will look like this:

let
    Source = Sql.Database("mycomputername\sqlserver2017", "wideworldimportersdw"),
    Fact_Sale = Source{[Schema="Fact",Item="Sale"]}[Data]
in
    Fact_Sale

Basically it's possible to adjust the Source line from the 1st query and add a 2nd line that does the navigation like so:

let
    //Source = Sql.Database("mycomputername\sqlserver2017", "wideworldimportersdw", [Query="execute dbo.a_simple_select"])
    Source = Sql.Database("mycomputername\sqlserver2017", "wideworldimportersdw"),
    Fact_Sale = Source{[Schema="Fact",Item="Sale"]}[Data]
in
    Fact_Sale

After this adjustment, the 1st query also has a Navigation step. Please make sure that the object returns the same column names as they have been returned by the stored procedure, otherwise your visuals will break.

I urge you to try this in a copy of a working Power BI Desktop file, as things might break

Hopefully this provides what you are looking for.

Regards,

Tom

[mnawaz]

Thanks Tom, that solution worked.