'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Make Power Query Tables Available in Excel Power Pivot
Question details
The user needs to make tables imported via Power Query visible and usable within the Excel Power Pivot Data Model.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Setting up a data model for advanced reporting, relationships, and DAX measures.
- Observed behavior
- Tables imported through Power Query appear in the Power Query Editor but do not show up in Power Pivot until explicitly loaded into the Excel Data Model.
Ensure you have completed all necessary data transformations in the Power Query Editor and that the Power Pivot add-in is enabled in Excel.
Change Query Load Settings to Add to Data Model
Adjust the existing query's load destination settings to include the data in Excel's Data Model, making it accessible in Power Pivot.
By default, Power Query may only create a background connection or load the table into a standard Excel worksheet. To build relationships and measures in Power Pivot, the data must be specifically routed to the Data Model.
Navigate to the 'Data' tab on the Excel ribbon and click on 'Queries & Connections' to open the side pane.
Right-click the specific query you want to use in Power Pivot and select 'Load To...' from the context menu.
In the Import Data dialog box, ensure the checkbox for 'Add this data to the Data Model' is checked, then click OK.
Wait for the query to refresh. Once completed, go to the 'Power Pivot' tab on the ribbon and click 'Manage' to verify that your table is now available for relationships and data-model reporting.
Try WPS Office for Your Daily Data Analysis
While Power Pivot is a specialized feature of Microsoft Excel, WPS Office provides a highly compatible, lightweight alternative for everyday spreadsheet tasks, pivot tables, and data analysis without the high subscription costs.
- 1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx workbooks.
- 3. Use Pivot Tables: Go to the 'Insert' tab and click 'PivotTable' to analyze and summarize your data efficiently.
Frequently Asked Questions
Why is my Power Query table not showing up in Power Pivot?
Power Query might only be set to create a connection or load data to an Excel worksheet by default. You must explicitly check 'Add this data to the Data Model' in the query's 'Load To' settings for it to appear in Power Pivot.
Can I edit the data directly inside Power Pivot?
No, if the table was imported and shaped using Power Query, its connection and data transformations must be managed within the Power Query Editor. Power Pivot is used for creating relationships and measures, not editing the raw query steps.
Where do I find the 'Load To' options for an existing query?
You can access these options by going to the 'Data' tab, clicking 'Queries & Connections', and then right-clicking your specific query in the right-hand pane to select 'Load To...'.
