cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mnawaz Frequent Visitor
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Super User
Super User

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

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

mnawaz Frequent Visitor
Frequent Visitor

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

Thanks Tom, that solution worked. 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 449 members 4,590 guests
Please welcome our newest community members: