How to Fix Power Query Returning Null Values from Excel in SharePoint
Question details
The user is attempting to import data from Excel files located in a SharePoint folder using Power Query, but the query returns null values for existing data, particularly in specific columns like Job (WOS).
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing multiple Excel worksheets from a SharePoint folder into Power Query for data combination and analysis.
- Observed behavior
- Values that are visibly present in the source Excel files are incorrectly returned as nulls in the Power Query editor, likely due to data type conversion mismatches.
Ensure you have proper access permissions to the SharePoint folder and open the source Excel files directly to verify if the missing data contains mixed formats like both letters and numbers.
Adjust the Column Data Type to Text in Power Query
Change the default data type of the affected column to 'Text' to prevent Power Query from converting non-numeric characters to null.
Power Query automatically attempts to detect and apply data types during the import process. If a column is assigned an integer data type (Int64.Type), but the actual Excel file contains text or mixed alphanumeric values in some rows, Power Query will fail to parse those cells and replace them with nulls.
Open your Excel workbook, go to the 'Data' tab on the ribbon, and select 'Get Data' > 'Launch Power Query Editor'.
In the Power Query Editor, look at the 'Applied Steps' pane on the right side of the screen and locate the 'Changed Type' step.
Click the small data type icon located on the left side of the affected column header (e.g., the Job (WOS) column). Select 'Text' from the dropdown menu.
If a prompt appears asking to change the column type, choose 'Replace current' to overwrite the automatic step, or add it as a new step if preferred. Verify that the null values have been replaced with the actual data.
Verify and Clean Source Data Consistency in Excel
Ensure the original data in the SharePoint Excel files is uniformly formatted to avoid confusing the Power Query engine.
Try WPS Office for Seamless Spreadsheet Management
If you frequently encounter complex data import errors or heavy system requirements with Microsoft Office, consider switching to WPS Office. It is a free, lightweight alternative that offers excellent compatibility with Excel formats, intuitive data processing tools, and a user-friendly interface that lets you get your work done faster.
- 1. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Install the Software: Run the downloaded file and follow the simple on-screen instructions to complete the installation.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and seamlessly open your existing .xlsx files to continue working without interruptions.
Frequently Asked Questions
Why does Power Query return null when importing from SharePoint?
Power Query often returns nulls when it encounters a data type mismatch. For example, if a column is automatically set to accept only whole numbers (Int64.Type), but the source Excel file in SharePoint contains text, letters, or mixed characters in certain rows, Power Query will convert the non-conforming data to null.
How do I refresh my Power Query data after fixing the source file?
To refresh your data, open the Power Query Editor or go to your Excel workbook. Click the 'Data' tab on the ribbon, and select 'Refresh All'. This will pull the latest data from the SharePoint folder and apply your updated transformation steps.
Can I prevent Power Query from automatically changing data types?
Yes. You can either delete the automatic 'Changed Type' step in the 'Applied Steps' pane of the Power Query Editor, or you can disable the feature globally. To disable it, go to File > Options and settings > Query Options > Data Load, and select 'Never detect column types and headers for unstructured sources'.
