Data Mashup Explorer allows you to explore the Power Query mashups and supporting details contained in a Microsoft Excel (xlsx, xlsm, xlsb) or Microsoft Power BI file (pbix, pbit). Primarily, this tool is useful for learning about the little known but non-secret file format used by these Microsoft applications to save mashups.
The user interface is simple. On the left, an Explorer-like tree lists the various components in the data mashup’s binary stream. Select one, and its contents are displayed on the right. For details on these various components, see (post coming soon about the underlying file format).
Special handling is given to Metadata, whose native format (a combination of XML, JSON and several binary formats) is complex and verbose. Metadata’s tab JSON presents a unified and simplified view with almost all binary formats translated and with repetition and extra syntax minimized.
Related: To import and export Power Query using PowerShell, check out Data Mashup Cmdlets.
Note: For Power BI files only (both PBIX and PBIT files; does not apply to Excel files)—When Microsoft Power BI’s “Store datasets using enhanced metadata format” preview feature is enabled, Power Queries are saved in a format that is not publicly documented. Unfortunately, this means that this tool cannot read mashups directly out of these files.
If the tool misbehaves on you, please drop me an email. If you have an idea for a feature or improvement, you’re welcome to add your suggestion below as a comment or email.
- 0.1.1-beta (2020-03-30)—Initial Release
- 0.1.2-beta (2020-04-14)—Added JSON translation rules for the following metadata elements: IsLastAnalysisServicesFormulaTextCollection, LoadedToAnalysisServices, LoadToReportDisabled & RefreshOnRefreshAll
- 0.2.0-beta (2020-05-01)—Expands embedded contents (i.e. content used by
Embedded.Value), allowing individual component items to be viewed.
- 0.3.0-beta (2020-06-08)—Added basic refresh (File > Refresh or
Ctrl+R) functionality which reloads the currently open file.
- 0.3.1-beta (2020-08-12)—Added support for reading Excel macro-enabled (xlsm) workbooks.
- 0.3.2-beta (2020-09-17)—Added support for reading Excel binary (xlsb) workbooks.
For notification of new versions, follow @bgribaudo on Twitter.