Previously working Direct Query reference no longer works

[NeilWalker-RBA]

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. '. 

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

[NeilWalker-RBA]

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]

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.

Best regards,
Community Support Team_ Scott Chang

[NeilWalker-RBA]

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]

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]

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]

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