Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

freder1ck

DirectQuery errors on imported tables. July 2018 Composite Models.

Submitted by Kudo Kingpin on ‎07-24-2018 07:40 AM

 

direct query errors.png

So, I turned Composite Models preview feature on yesterday to review it. I got the top two errors on my imported tables.

I turned it off and restarted PBID and refreshed all. Then, I got the third error on a different table. I also continue to get the error on the other table.

 

 

 

Status: New
See more ideas labeled with:
26 Comments (26 New)
Comments
edhans
Community Champion
Community Champion
‎07-24-2018 02:05 PM

Same. I had to restore my PBIX file to an older version. The desktop app thinks a query is DirectQuery after turning on Composite Model even though it isn't - and hovering over it shows it is IMPORT, not DIRECTQUERY. But whatever metadata is in that file, it got changed and now the file won't refresh at all.

tonymaclaren
Helper I
Helper I
‎07-24-2018 11:58 PM

This is absolutely infuriating !Please help.

I had  perfectly working models with the same M query generated date tables in them. (The date table does not refer to an external source at all !! ) On upgrading to the July 2018 release,my date table query no longer works and displays the same errors above. Power BI then asks if I would like to convert all tables from DIRECTQUERY to IMPORT, which I did. no error...but then it asks again and again and again the same thing with no effect.Turning off composite models and restarting has no effect. So like edhans I reverted to the June version, but horror of horrors the file wont open because it is now incompatible with the new version. I suspect Miscrosof delayed this latest release because of the composite models feature, well, they should have delayed it longer because it has caused a disaster for me.

 

 

 

Anonymous
Not applicable
‎07-25-2018 01:09 AM

Indeed, I have the same problem! I just updated to the July version and now I can't refresh my reports anymore.

tonymaclaren
Helper I
Helper I
‎07-25-2018 02:08 AM

Anyone who has a M query date table is in big trouble.

But I have a hack that worked for my date table.

1. I disabled composite models in options and settings.

2. I went to the advanced editor and copied the text of trhe query.

3. I deleted the date table

4. I started a new blank query opened the advanced editor and deleted the default text so I had a blank 

5. I pasted the original date query into the advanced editor

6. Problem solved

This may work for other queries but I have not tried it.

tonymaclaren
Helper I
Helper I
‎07-25-2018 02:13 AM

Just to be clear in step 1 of my previous you must close and then restart the application.

edhans
Community Champion
Community Champion
‎07-25-2018 04:51 AM
This clearly isn't ready for prime time, but that is why it is in preview status. If you can, open a support ticket and include a damaged PBIX file for MS to investigate, or turn off the feature and restore any impacted PBIX files from backup. I just restored previous versions of mine from Sharepoint and disable the feature, but I did submit one to MS to investigate.
tstewart
Regular Visitor
‎07-25-2018 06:52 AM

Same/Similar issue 

 

Working model which I opened and saved after I'd enabled the Preview feature

From that point, even if disabling the Preview it is treating all new imported tables as DQ even if they are explicitly selected to be Import. 

And if I open that model now with Preview Composite disabled it won't let me, message that because it was saved with Preview enabled it must be opened with Preview enabled. 

Going to have to roll back to earlier version and hope it it isn't too much work to get back to where I need to be

tonymaclaren
Helper I
Helper I
‎07-25-2018 10:06 AM

You have my sympathy. I have now discovered another wierd feature. My custom date table loads ok and PBI checks it and allows it to be marked as a date table, but does not work properly any any more. An earlier file saved in the June release  with the same date table works without a problem!! I then built a completelty new PBIX file from scratch ran the date table query-no errors of any kind, but I cannot get any dates to show. Any more news on this rabit hole would be appreciated.

freder1ck
Kudo Kingpin
Kudo Kingpin
‎07-25-2018 12:09 PM

Ok. What I found is that one of my tables was invisibly marked as DirectQuery.

( I looked in the PBIX zip file in DataMashup and found <Entry Type="IsDirectQuery" Value="l1" /> instead of Value="l0"

When I edited it in the query editor, it had a warning.

To fix it, I copied the M from the Advanced Editor and pasted into a new blank query (advanced).

That query did not have the warning, so I copied it again and pasted it back into the original query and it worked. It looks to me like the conversion is missing a couple of items...

 

 

tstewart
Regular Visitor
‎07-25-2018 12:41 PM

Completely rebuilt the model - took 5 hours.

The offending table if I import it, transform on the imported table then no issue. But if I Reference it or Create New with Merge or Append then that table only brings back 1000 rows.

Having used the Import and no Transforms that table works
But the table that WAS working, but with which I reference and do some cleanup that is now only returning 1000 rows. I set the import date filter to all after 1st Feb to force an earlier date and it now returns 2000 rows. But its missing the other 387k 

Looked at the Mashup and all my IsDirectQuery entries are "10" not "11"

It seems to be if I have a custom date table (which I do because I need time intelligence and financial year on this report) then I can't do any transforms that aren't supported in DQ or else the number of rows imported is cut off. 

So now the table that was causing problems is working fine - import into table, use that table without any transforms as the Load and all ~400k rows return. But the table that set up in *exactly the same way* in the older model is now the one which is not playing nice

There's something extremely odd going on here. Its like its query folding for me but with no instructions to do anything from me and with the tables being set to direct import


Idea Statuses