Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Previously working Direct Query reference no longer works

Hello!

 

A week ago I published a working BI model the Fabric with no issues.  This week the Power Query data model in Power BI Desktop Version: 2.123.742.0 64-bit (November 2023) will not load into the report with this error:

 

Failed to save modifications to the server. Error returned: 'DirectQuery partition <Table><GUID> has '0' datasource reference(s) in its expression which is not allowed. '. 

NeilWalkerRBA_0-1701295776425.png

The tables it's complaining about all refer to Direct Query tables in the model (the Reference option in the query editor).  Until yesterday these all parsed/loaded just fine.  I'm facing having to rebuild the entire PBIX, as deleting the tables or making any change to the data model always produces the above error.  I've Bing'ed the issue of course

 

Any help appreciated, many thanks!

 

Neil

Status: Accepted

Hi @NeilWalker-RBA ,

 

I think you should merge the queries and try to use a let and in as much as possible, otherwise when you reload, the editor won't recognize the source data, resulting in an error being reported. It might be difficult to integrate, you can avoid this operation as you said.

 

Best regards,
Community Support Team_ Scott Chang

Comments
NeilWalker-RBA
Regular Visitor

To give a sense of how trapped I am, I deleted the whole data model in query editor and it still produces the error

v-tianyich-msft
Community Support
Status changed to: Investigating

Hi @NeilWalker-RBA ,

 

Please check the screenshot of my test below, I wasn't able to reproduce your problem.

It would be great to have a detailed info to describe your problem. Since this is a public community, please be careful not to include sensitive information.

vtianyichmsft_1-1701329687288.png

 

vtianyichmsft_2-1701329723322.png

 

Best regards,
Community Support Team_ Scott Chang

 

NeilWalker-RBA
Regular Visitor

Hi Scott, thanks for the assist

 

This is one of the DQ queries:

let
Source = CommonDataService.Database(<dataverse>, []),
dbo_xxxx_projectbusinessunit = Source{[Schema="dbo",Item="xxxx_projectbusinessunit"]}[Data]
in
dbo_xxxx_projectbusinessunit

 

This is the query that references the above

 

let
Source = xxxx_projectbusinessunit,
#"Expanded msdyn_project" = Table.ExpandTableColumn(Source, "msdyn_project", {"msdyn_projectid"}, {"msdyn_projectid"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded msdyn_project",{<array of columns>})
in
#"Removed Columns"

NeilWalker-RBA
Regular Visitor

I isolated just those two queries in a new PBIX and couldn't reproduce the error.  If I copy all the queries from the problem PBIX into a new PBIX, I get the error on the second load.  So first time it processes the queries fine, then I make an adjustment in the data model to force a second load and get the error.

 

I'm going with corruption\updated DQ rules and rebuilding to avoid DQ references, unless you have any smarter ideas

 

Cheers

 

Neil

v-tianyich-msft
Community Support
Status changed to: Accepted

Hi @NeilWalker-RBA ,

 

I think you should merge the queries and try to use a let and in as much as possible, otherwise when you reload, the editor won't recognize the source data, resulting in an error being reported. It might be difficult to integrate, you can avoid this operation as you said.

 

Best regards,
Community Support Team_ Scott Chang

nwalker
New Member

Thanks for the idea Scott.  I rebuilt it without the references, only took a few hours.  I've bookmarked this page in case the error resurfaces and will try your idea