'%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 Split MongoDB ODBC Data into Multiple Power Query Tables
Question details
The user wants to distribute data retrieved from a single MongoDB ODBC connection into multiple separate tables within Power Query, while ensuring the original connection remains active and efficient.
- Product
- Excel / Power BI
- Device & OS
- not provided
- Scenario
- Importing and transforming complex, nested MongoDB database structures via an ODBC connection into separate, manageable relational tables.
- Observed behavior
- Looking for the correct Power Query workflow to route a single ODBC data source into multiple destination tables without duplicating external queries or breaking the live connection.
Ensure you have the correct MongoDB ODBC driver installed and configured in your system's ODBC Data Source Administrator, and that you have valid credentials to securely access the database.
Use a Staging Query and Query References
This is the most efficient method to split data. By creating one main connection and referencing it for multiple tables, you keep the ODBC connection active without querying the database multiple times.
Power Query allows you to create a primary 'staging' query that holds the active connection to your MongoDB ODBC source. You can then create dependent queries (references) that branch off from this staging query to form separate tables.
Open Power Query, click 'Get Data', select 'From Other Sources', and choose 'From ODBC'. Select your MongoDB ODBC Data Source Name (DSN) and enter your credentials to import the data.
Once the data loads in the Power Query Editor, do not perform heavy transformations yet. Right-click this query in the Queries pane, select 'Properties', and uncheck 'Enable load to report/worksheet'. Rename it to 'MongoDB_Staging'.
Right-click the 'MongoDB_Staging' query and select 'Reference'. This creates a new query linked to the staging data. Rename this new query to represent your first split table (e.g., 'Customer_Table').
Apply filters, remove unnecessary columns, and expand JSON/MongoDB records in the 'Customer_Table' query. Repeat the 'Reference' step on the staging query to create additional separate tables as needed.
Click 'Close & Load To...' and ensure your referenced queries are set to load as Tables into your Excel worksheet or Power BI data model.
Consult the Microsoft Fabric Community for Complex Layouts
Use this solution if you encounter ODBC driver compatibility issues or need highly advanced M-code routing that standard referencing cannot handle.
Experience Lightweight Data Analysis with WPS Office
While advanced ODBC integrations and Power Query M-scripting are heavily tied to the Microsoft ecosystem, WPS Office provides an incredibly fast, highly compatible alternative for everyday data processing. Once your database data is extracted to CSV or Excel formats, WPS Spreadsheet offers powerful data manipulation without the heavy resource usage.
- 1. Download WPS Office: Visit the official WPS website and download the free version of WPS Office.
- 2. Open Your Data File: Launch WPS Spreadsheet and open your exported MongoDB data (CSV or XLSX format).
- 3. Analyze and Organize: Use WPS Office's built-in filtering, PivotTables, and advanced formulas to effortlessly analyze your dataset.
Frequently Asked Questions
Can I keep the ODBC connection active while splitting tables in Power Query?
Yes. By using a single staging query connected to the ODBC source and creating multiple 'Referenced' queries from it, the connection remains active and centralized. Power Query will fetch the data once and distribute it to the referenced tables.
Why is my MongoDB ODBC connection running slowly in Power Query?
MongoDB is a NoSQL database, and pulling large, unoptimized, nested documents through an ODBC driver can cause performance bottlenecks. Ensure you filter the data as early as possible in your query, ideally passing native SQL queries directly to the ODBC driver if supported.
Does WPS Office support Microsoft Power Query?
WPS Office offers standard external data import capabilities (like importing from text, CSV, and basic web queries) and excellent PivotTable support, but for advanced M-language scripting and specialized ODBC transformations, Microsoft Excel or Power BI is required. You can, however, easily process the exported datasets in WPS Spreadsheet.
