How to Filter Dataverse Data in Power Query Using an Excel List
Question details
The user needs to filter Dataverse material records in Power Query based on a list of IDs stored in an external Excel part list, aiming to return only matching rows.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Connecting to Dataverse data in Power Query and filtering it using an external Excel list to avoid loading large, unnecessary datasets.
- Observed behavior
- Loading all Dataverse records fetches thousands of unnecessary rows instead of just the ones matching the Excel part list IDs.
Ensure that both your Excel part list table and the Dataverse query use identical data types (e.g., both formatted as Text or both as Whole Number) for their respective ID columns before applying the filter.
Use List.Contains in the Advanced Editor
Filter the Dataverse query by checking if each material ID exists in the Excel part list using the List.Contains function.
This approach uses a custom M code snippet to dynamically check the Excel list against the Dataverse records, ensuring only the relevant IDs are fetched.
Import your Excel part list into Power Query. Rename this query to 'Partlist' and ensure the column containing the IDs is appropriately named 'ID'.
Connect to your Dataverse environment and open the target data table in the Power Query Editor.
Navigate to the Home tab and click 'Advanced Editor'. Add a new step to filter the rows by typing: Result = Table.SelectRows(Source, each List.Contains(Partlist[ID], [ID])). Replace 'Source' with the exact name of your previous step if it differs.
Click 'Done' in the Advanced Editor to apply the step, then click 'Close & Load' on the Home tab to output the filtered Dataverse data into your spreadsheet.
Filter Using Merge Queries (Inner Join)
Use the visual Merge Queries feature as an alternative to writing M code, joining the Dataverse table and the Excel list.
Manage and Filter Large Datasets effortlessly with WPS Office
If you are working with extensive data lists and need a fast, reliable, and lightweight spreadsheet tool, WPS Office is an exceptional choice. It offers powerful built-in data filtering capabilities and is fully compatible with Microsoft Excel formats, making your workflow smoother and more cost-effective.
Frequently Asked Questions
Why does my filter step return an Expression.Error in Power Query?
This error typically occurs when there is a data type mismatch between the Excel list and the Dataverse table. Ensure both ID columns are set to the exact same data type (e.g., both set to Text or both set to Whole Number) by clicking the data type icon in the column header.
Can I use this method if my Excel part list contains thousands of IDs?
Yes, using List.Contains or an Inner Join works perfectly for thousands of IDs. However, be aware that filtering against an external Excel file may break query folding, causing Power Query to process the dataset locally rather than on the Dataverse server.
How do I turn my Excel column into a list so List.Contains will work?
You do not need an extra conversion step. When you use the syntax Partlist[ID] in your M code, Power Query automatically interprets the 'ID' column from the 'Partlist' query as a list, making it instantly compatible with the List.Contains function.
