cancel
Showing results for 
Search instead for 
Did you mean: 

Error on apply Power Query: Expression in partition n/a in table n/a references an unknown entity

let
Source = AzureStorage.DataLake("https://xxxxx.dfs.core.windows.net/contoso-retail-dw/rssfeed/rawdata/date=" & DateTime.ToText(Date.AddDays(DateTime.LocalNow(), -1), "yyyy-MM-dd")),

 

This PowerQuery Source gives a preview without any error, but when "close and apply" this error is shown:

Failed to save modifications to the server. Expression in partition n/a in table n/a references an unknown entity

 

When I change the source to static string it works fine!?

 

let
Source = AzureStorage.DataLake("https://xxxxx.dfs.core.windows.net/contoso-retail-dw/rssfeed/rawdata/date=" & "2020-03-29"),

Status: New
Comments
Frequent Visitor

Hmm. I've tried a couple times with version 2.80.5803.1061 64-bit (April 2020) and I'm still getting the same error.

 

1. Open blank Power BI document.

2. Enable Enhanced Metadata Format.

3. Close Power BI.

4. Open existing report.

5. Upgrade report to use Enhanced Metadata Format.

6. Refresh.

7. Add new table to Power Query and Close and Apply.

8. "Failed to save modifications to the server. Error returned: 'Expression in partition 'n/a' in table 'n/a' references an unknown entity.'."

 
 
Helper II

Im getting the same error.  Downloading the Apr2020 update now.  Will let you know if it's a fix.

Frequent Visitor

Same here, still getting the same error after upgrading to April2020 release (I've removed web query, disabled 'enhanced metadata format', re-opened file after upgrade, inserted web-query). This issue has obiously not been fixed.

Helper II

OK - Finally got it worked out.

  1. Open your old broken pbix file.
  2. Turn Off 'enhanced metadata format'
  3. Copy all your queries out to text files.  (Quick way is to just right click on the query name in the left side of the query editor, select "copy" then click in a text editor, I use Notepad++, and hit Ctrl+V.  The whole query will be pasted into the enditor.  In Notepad++, you can add multiple tabs for each file, which is nice.)
  4. Close PBI and Start it up with a new file.
  5. Check the settings again to be sure 'enhanced metadata format' is really turned off. (First time I did this, it wasn't, which caused me to have to start all over again - bummer... )
  6. Save the new file with whatever name / revision number you need.
  7. Open the query enditor and add blank queries as needed.  Use the advanced editor to paste from your text file into the new query.  Be sure to rename them.  Pay close attention to the creation order as some of your queries will, no doubt depend on others.  Take a screen shot of your query relationships if it helps.
  8. I saved and refreshed after the "Functions" and "Connection Only" queries were created.  As these don't get loaded into the model, I felt rather safe in that all of them were not causing the problem.
  9. I then saved after each dimension and transaction query were created and refreshed, just to be sure if something broke i woudl know which query was causing the issue.  I also saved several versions "...Rev1, ...Rev2, etc. so if something did break, I could go back easily to the last "unbroken" file.
  10. Fix up your Calendar table.
  11. Create your relaitionships.
  12. Recreate all your measures.
  13. Recreate all yoru visuals / reports.
  14. I did not turn the 'enhanced metadata format' back on.  I figure if the model works without it, why break it!  I'll give it a few more revisions and try it again.  (on a test file!).

Hope this helps...

 

 

Super User III

Hi @v-qiuyu-msft ,

any new on this issue?
Don't understand why this issue has not been accepted.

 

Super User II

I'm not sure if this is the same issue, but it may be related. Scenario:

  1. New file with Enhanced Metadata enabled. Worked for less than 15min in Power Query to get connections to SharePoint sites. This was a March 2020 file I am 99% sure.
  2. Put file aside for a few days. Worked in April 2020 version for 4.5hrs. Constantly saving, and SharePoint (via OneDrive for Business) making version backups as I go.
  3. Have basic data mode the way I want it. Close file go eat lunch.
  4. Come back, open file and refresh my data. All of my test visuals (just 3) went blank with errors. none of the data was there. Opened up Power Query, and all M code was from 4-5 days ago! All of my M code was gone!
  5. Restored a version. Again, all data is there in the data model. Open up Power Query, almost no M code. All from 4-5 days ago again.

 

Nothing I did brought it back. I had to start from scratch basically, which I did in a new file with enhanced metadata turned off.

Frequent Visitor

I think I've solved this, at least for my case. After removing option to use enhanced metadata format, I've re-written my web query, now using Web.Content() and Html.Table(). Here's a summary of my M-code (sorry, there is some norwegian here):

let
    Helligdager = (Year) => let
        Source = Web.Contents("https://www.wincalendar.com/kalender/", [RelativePath =  "Norge-" & Number.ToText(Year)]),
        Data_ = Html.Table(Source, {{"Column1", "TABLE.thlinks > * > TR > :nth-child(1)"}, {"Column2", "TABLE.thlinks > * > TR > :nth-child(2)"}, {"Column3", "TABLE.thlinks > * > TR > :nth-child(3)"}, {"Column4", "TABLE.thlinks > * > TR > :nth-child(4)"}}, [RowSelector="TABLE.thlinks > * > TR"]),
        Out = Table.AddColumn(Data_, "År", each Year)
        in Out,
    cY = Date.Year(DateTime.LocalNow()),	
    #"Tilføyet spørringer" = Table.Combine({Helligdager(cY-1), Helligdager(cY), Helligdager(cY+1), Helligdager(cY+2), Helligdager(cY+3), Helligdager(cY+4), Helligdager(cY+5) }),
    ...
in
    ...

 As you see, the Html.Table() is complicated. I didn't write this myself; I did the following:

  1. Enabled preview feature 'new web query'.
  2. Add new source from web.
  3. Using the GUI tool, select the correct table.
  4. In advanced editor, copy the two lines containing Web.BrowserContent() and Html.Table() 
  5. Open andvanced editor for old web-source, and replace lines for Source and Data.
  6. Important: Change Web.BrowserContent() method to Web.Content()

The use of 'RelativePath' inside a custom function also solves the issue of scheduled refresh in power bi service. I reccommend reading this blog post for details: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

Power BI Team

Hi everyone, thanks for the feedback here!

 

The "enhanced metadata format" preview feature does not yet support dynamic datasources, but we're working on it and expect it to be supported in the next month or so. A dynamic datasource is one where the query's source changes based on a function input, parameter, or, as is the case in the original post, a volatile function. This is why using the static source string succeeds. We are also working on improving these error messages to better identify what exactly is going wrong. 

 

A few other notes on the "enhanced metadata format" preview:

  • Once you enable the preview feature, any report that is opened will be auto-upgraded to the new format. For now, while we're still in preview, if the upgrade fails it will silently fall back to the old format. Any new reports created with the feature enabled will automatically be generated in the new format. The upgrade is irreversible. We do save a backup of the pbix before upgrading, which you can find at "C:\Users\<user>\AppData\Local\Microsoft\Power BI Desktop\TempSaves\Backups". This won't have any of the changes you've made since upgrade, but might be a better starting point for you. Make sure you disable the preview feature before opening the backup file. 
  • Previously, only tables that were loaded to the Report were persisted in the datamodel, but with this new format we save all queries to the datamodel even if they aren't loaded. This is why @ImkeF hit the same error when applying changes even when all of the queries were not loaded as tables. 

 

While we understand that the current limitations of the new metadata format preview are blockers for some, we greatly appreciate each of you who have opted-in and are providing feedback. This new metatdata format will open the doors for many future modeling scenarios, and we're invested in making the upgrade as smooth as possible. If you experience other issues related to the "enhanced metadata format" preview, please reach out to me via DM or tag in a post. 

Power BI Team

@edhans, your issue looks different, and it would really help us investigate if you're able to share your pbix file, or if you have consistent repro steps. Please DM me and I'd be happy to look into the issue.  

Frequent Visitor

@megank , thanks for the clarification. That makes sense now why my PBIX got corrupted as we are using a dynamic source with parameters so we can switch from DEV to PROD easily. It would be nice to see that clarication documented here: https://docs.microsoft.com/en-us/power-bi/desktop-enhanced-dataset-metadata.

 

Thanks again.

 

AJG