r/dataanalysis • • 1d ago

Headless Excel Data Model with Python

I’ve released Excel Data Model Lab, an open-source Python library for creating and editing embedded Excel Power Pivot data models without Excel installed.

You can populate models with data from Python or configure supported external data connections. Build tables, relationships and measures, then add model-backed PivotTables and charts. The data can live entirely inside the model; worksheet copies aren’t required.

The demo includes 24,000 synthetic sales across six related tables, plus slicers, a timeline and CUBE formulas. The library creates the workbook headlessly; Excel handles calculation, report rendering and refreshing configured connections.

This is an early release candidate, and I’d welcome feedback from people building automated Excel reports. Compatibility reports, feature requests and examples of workflows this could simplify would all be helpful.

GitHub and demo: https://github.com/sean-palmer-coding/excel-data-model-lab
PyPI: https://pypi.org/project/excel-data-model-lab/

0 Upvotes

3 comments sorted by

1

u/13ass13ass 1d ago

I’m pretty happy with power query and haven’t bothered much with power pivot. What can power pivot do that I’m missing with power query?

2

u/More-Cucumber-1066 9h ago

I think of them as complementary. Power Query gets the data into shape. Power Pivot lets you relate those tables in Excel’s Data Model, write measures, and use fields from different tables in the same PivotTable. If you’re familiar with Power BI, it works a lot like a semantic model there.

My favorite part of the Data Model is CUBE formulas. You can pull a measure into whatever cell you want, so you can build a report that looks the way you want without being tied to a PivotTable layout.

Separating facts and dimensions can also make a workbook leaner, especially if it means you’re not merging the same lookup columns into a huge flat table. For a simple report off one table, though, Power Query and a regular PivotTable might be all you need.

1

u/AutoModerator 1d ago

Automod prevents all posts from being displayed until moderators have reviewed them. Do not delete your post or there will be nothing for the mods to review. Mods selectively choose what is permitted to be posted in r/DataAnalysis.

If your post involves Career-focused questions, including resume reviews, how to learn DA and how to get into a DA job, then the post does not belong here, but instead belongs in our sister-subreddit, r/DataAnalysisCareers.

Have you read the rules?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.