Cause of data refresh issue

[Greg_Deckler]

Really just looking for confirmation on this. I have a a data set in Azure SQL DB with about 6 queries that in Power BI for O365 (Power BI 1.0) was refreshing all of the queries automatically except for one that uses an "exec" SQL call which had to be refreshed manually. Recently moved this to Power BI 2.0 but the scheduled refresh essentially refuses to refresh any of the queries now because it says that 1 or more of the queries does not support refresh. I'm betting I know which query is causing this but just looking for confirmation that it is the query that uses the "exec" SQL call before we spend time re-engineering that query.

[Vicky_Song]

@Greg_Deckler, we tested on our side that it is possible to refresh a dataset with query connecting to Azure SQL database with “EXEC” statement.

As you have more than 1 query, how about you first refresh it in Power BI desktop? Then go Edit Queries -> Advanced Editor to find out which query cause the refresh failure? 

[Greg_Deckler]

@Vicky_Song OK, I tracked down the issue and am a little surprised. The problem seems to be a query that references a table in the Excel workbook. The query code is:

let

    Source = Excel.CurrentWorkbook(){[Name="Projections"]}[Content]

in

    Source

Removing that query remove the error. Is this something that is a known issue or otherwise known to not be supported or should I log this as an Issue?

[Greg_Deckler]

Thanks for following-up Vicky. The issue is that they all work from Excel. Let me back-up. The file is an Excel file built with Power Query. Had to do it this way due to customer interface constraints. Loaded Excel file on OneDrive for Business and connected it to Power BI (not import). When going to Schedule Refresh, it just says "one of your queries does not support refresh". The old O365 Power BI would refesh all of them automatically except the EXEC query. If necessary, I can probably get someone at Power BI access to the Power BI tenant, etc.

Perhaps I need to open up a formal support ticket for this?