I have an Azure AI Knowledge store, the data is generated in Azure Storage Tables. When I try to query the data using Power Query in Power BI Desktop I'm getting intermittent errors of the form:

DataFormat.Error: OData: The format 'application/json;odata=fullmetadata;streaming=true;charset=utf-8' is not supported. Details: application/json;odata=fullmetadata;streaming=true;charset=utf-8

This is the Power Query:

let
    Source = AzureStorage.Tables(KnowledgeStoreStorageAccount),
    ThisTable = Table.Buffer(Source{[Name=GetTableName("Document")]}[Data]),
    RemovedColumns = Table.RemoveColumns(ThisTable,ColumnsToRemove),
    ExpandedContent = Table.ExpandRecordColumn(RemovedColumns, "Content", Record.FieldNames(RemovedColumns{0}[Content]))
in
    ExpandedContent

I work out the table name using the GetTableName function, I get the same error with a hardcoded table name. The last two steps are there for completeness, they don't effect the occurence of the error. It's some sort of buffering problem, I tried using Table.Buffer to no avail. It seems the AzureStorage.Tables is having the issue internally on an intermittent basis. Referesh will clear the problem eventually. It's a very small data volume a few hundred rows at most. PowerQuery Online has the same issues.

I have an Azure AI Knowledge store, the data is generated in Azure Storage Tables. When I try to query the data using Power Query in Power BI Desktop I'm getting intermittent errors of the form:

DataFormat.Error: OData: The format 'application/json;odata=fullmetadata;streaming=true;charset=utf-8' is not supported. Details: application/json;odata=fullmetadata;streaming=true;charset=utf-8

This is the Power Query:

let
    Source = AzureStorage.Tables(KnowledgeStoreStorageAccount),
    ThisTable = Table.Buffer(Source{[Name=GetTableName("Document")]}[Data]),
    RemovedColumns = Table.RemoveColumns(ThisTable,ColumnsToRemove),
    ExpandedContent = Table.ExpandRecordColumn(RemovedColumns, "Content", Record.FieldNames(RemovedColumns{0}[Content]))
in
    ExpandedContent

I work out the table name using the GetTableName function, I get the same error with a hardcoded table name. The last two steps are there for completeness, they don't effect the occurence of the error. It's some sort of buffering problem, I tried using Table.Buffer to no avail. It seems the AzureStorage.Tables is having the issue internally on an intermittent basis. Referesh will clear the problem eventually. It's a very small data volume a few hundred rows at most. PowerQuery Online has the same issues.

• azure
• powerbi
• powerquery
• powerbi-desktop
• azure-table-storage

1 Answer 1

Troubleshooting DataFormat.Error in Power BI with Azure Table Storage:

1. The default AzureStorage.Tables() function internally uses OData with full metadata, which Power BI does not fully support. So, update the query as following:

let
    Source = OData.Feed("https://storage_account_name.table.core.windows/"),
    ThisTable = Source{[Name=GetTableName("Table_name")]}[Data],
    RemovedColumns = Table.RemoveColumns(ThisTable, ColumnsToRemove),
    ExpandedContent = Table.ExpandRecordColumn(RemovedColumns, "Content", Record.FieldNames(RemovedColumns{0}[Content])),
    FinalTable = Table.Buffer(ExpandedContent)
in
    FinalTable

Here using OData.Feed() will enforce minimal metadata, hence avoiding unsupported formats issue.

• Further Steps:(Additional)

While applying this fix, I encountered another error Access to the resource is forbidden.

• Since we have switched to OData.Feed, authenticating via Access Key Authentication is required.

1. Get the Acess key from Azure Storage account.
2. Copy one of the Access key and go with Access Key Authentication by clicking on EDIT and save the applied changes.

• Go to Get Data, choose OData feed and give Azure Table Storage URL to fetch data.

https://<storage_account_name>.table.core.windows/