Fix Power BI Refresh Error: Cannot Convert Null to Type Record
Question details
The user needs to resolve a data refresh failure in published Power BI reports connected to Business Central, caused by a null to record conversion error in merged queries.
- Product
- Power BI
- Device & OS
- not provided
- Scenario
- Refreshing published Power BI datasets connected to Business Central data involving merged queries.
- Observed behavior
- The refresh process fails with the specific error message "We cannot convert the value null to type Record", despite no obvious null values being visible in the data.
Open your Power BI Desktop file containing the failing dataset and launch the Power Query Editor to inspect the query steps.
Inspect and Handle Nulls in Merged Query Expansions
Identify the specific step causing the error by checking your merged queries and handle unmatched rows before expanding records.
This error typically occurs during a Left Outer Join when no matching record is found, resulting in a 'null' instead of a nested table or record. Expanding this null value triggers the conversion error.
In the Power Query Editor, select the problematic query. Click through the 'Applied Steps' pane one by one until the error message appears in the data preview.
Select the step immediately prior to the failure (usually a 'Merged Queries' step). Look for rows in the newly merged column that contain 'null' instead of a 'Table' or 'Record' object.
Before the step that expands the column, either filter out the rows where the merged column is null, or use the 'Replace Values' feature to handle the empty records gracefully.
Click 'Close & Apply' in the Power Query Editor, then manually trigger a refresh in Power BI Desktop to ensure the error is resolved.
Consult the Microsoft Power BI Community
Seek specialized troubleshooting for Business Central connector behaviors from Power BI experts.
Need a Lightweight Alternative for Data Analysis? Try WPS Office
While WPS Office does not replace Power BI's complex data modeling, WPS Spreadsheet is a highly capable, free, and lightweight alternative for everyday data analysis, reporting, and pivot tables, fully compatible with Microsoft Excel formats.
- 1. Download the installer: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Install WPS Office: Run the setup file and follow the on-screen instructions to complete the installation.
- 3. Open WPS Spreadsheet: Launch WPS Office and select Spreadsheet to start analyzing your exported data with ease.
Frequently Asked Questions
Why does the 'Cannot convert null to type Record' error happen in Power BI?
It occurs when Power Query attempts to expand a column expecting a Table or Record object, but encounters a null value. This frequently happens after a Left Outer Join where some rows did not find a match in the secondary table.
How do I find exactly which step is causing the refresh failure in Power Query?
Open the Power Query Editor and select the query that is failing. Carefully click through each item in the 'Applied Steps' pane on the right side. The exact step that triggers the error will display the failure message in the main data preview window.
Can structural changes in Business Central cause Power BI refresh errors?
Yes. If a column name is altered, a table is restructured, or a data type is changed in Business Central, your existing Power Query steps may fail to map records properly, resulting in null values during joins and subsequent conversion errors.
