top of page
  • MK

Export Power BI Report Objects

Updated: Oct 28, 2021

Have you ever wondered which objects (tables, columns, measures etc.) are used within a Power BI report? How about which visual types are being used across a collection of Power BI reports? Or, if a report is using custom visuals? Well, you're probably not the only one looking for such information. However, it's generally quite a manual process to get this info as it requires monotonously navigating through each visual and filter on each page of the report (and of course for all reports). Doing this manually would take a very long time and probably would not even be accurate since it is such a long and tedious process. No longer!


The Method


Previously, I wrote a blog post on an automated method for exporting report-level measures and migrating them back to the data model. The solution for getting the report objects is quite similar - although a bit more involved. The parallel between these solutions is that they require 'hacking' into the Power BI Desktop (or Power BI Template) file and parsing through the 'Layout.json' file that is hidden beneath all Power BI reports. This 'Layout.json' file contains all the report-level metadata - including information on all the pages, visuals, filters, bookmarks, custom visuals, report-level measures and more.


The Solution


To streamline the process of collecting the report object metadata, I wrote a C# script which I have posted to GitHub. The script is to be executed in the Advanced Scripting window within Tabular Editor. It should be noted that in this capacity, Tabular Editor is functioning simply for the purpose of code-simplification and convenience (the model which is loaded into Tabular Editor before running this script does not matter).


Also, this script is able to collect the report objects for either a single Power BI report file or a collection of Power BI report files within a specified folder!


The Steps

  1. Download the C# script from GitHub.

  2. Copy and paste the C# script into the Advanced Scripting window in Tabular Editor.

  3. *Open/connect to any model in Tabular Editor (or even click 'File->New Model' to create a new model).

  4. Update the 'pbiFolderName' parameter to the folder which contains the Power BI report(s).

  5. If you want the script to only scan a single Power BI report, specify the report's file name (and extension) in the 'pbiFile' parameter. If you want the script to scan all the Power BI reports within the aforementioned folder, ensure this parameter is set to be blank: @"";

  6. If you want the report objects to be saved (to the same folder as specified in Step 4), set the 'saveToFile' parameter to 'true'. Otherwise, set this to 'false' (in this case the script will share the output in a series of pop-up windows).

  7. Click play (or press F5).

* Tabular Editor only allows you to run a C# script in the Advanced Scripting window if it is connected to a model. In this case, the script is not referencing the model which is loaded so it does not matter which model is actually loaded or if a new (blank) model is created (and then discarded).


The Output


The following shows the format of the output files:

  • Report Filters

    • Report Name, Filter Name, Table Name, Object Name, Object Type, Filter Type

  • Pages

    • Report Name, Page Id, Page Name, Page Number, Page Width, Page Height, Page Hidden Flag, Visual Count

  • Page Filters

    • Report Name, Page Id, Page Name, Filter Name, Table Name, Object Name, Object Type, Filter Type

  • Visuals

    • Report Name, Page Name, Visual Id, Visual Name, Visual Type, Custom Visual Flag, Visual Hidden Flag, X Coordinate, Y Coordinate, Z Coordinate, Visual Width, Visual Height, Object Count

  • Visual Filters

    • Report Name, Page Name, Visual Id, Table Name, Object Name, Object Type, Filter Type

  • Visual Objects

    • Report Name, Page Name, Visual Id, Visual Type, Custom Visual Flag, Table Name, Object Name, Object Type, Source

  • Custom Visuals

    • Report Name, Custom Visual Name

  • Bookmarks

    • Report Name, Bookmark Name, Bookmark Id, Page Id

  • Connections

    • Report Name, Server Name, Database Name, Report Id, Connection Type

  • Visual Interactions

    • Report Name, Page Name, Source Visual ID, Target Visual ID, Type ID, Type


Note: 'Source' within Visual Objects shows as 'Standard' for objects used within rows/columns etc. of visual itself. Objects used for conditional formatting or to set titles, backgrounds etc. will show as such within the 'Source' column.


Next Steps


There are some cool things which you can do once you have this information. For example, with this information you have a generally better understanding of which parts of your model are used more often (and perhaps which are used less often or not at all). You could leverage this information to either trim down your model to only the elements needed within the report(s) or potentially use the Master Model technique to dynamically generate 'mini-models' tailored for specific reports (with only the objects necessary to generate those reports).


Conclusion


This post continues a series of enabling metadata to be exported - whether its exporting model metadata, report-level measures, or report objects (as in this post). Gathering this information used to be quite challenging but now it's easy! Just note that this 'hacking' method is not officially supported by Microsoft - but it works!

24 Comments


Is there a way to run this without tabular editor?

Like
Replying to

Yes, stay tuned

Like

Raquel LM
Raquel LM
Jan 05

Thank you very mucho for this post. I would like to have also the Filter Details like (Age = >15) but your script don't contain it. I tried to write it my self but I think is very difficult depending on the type of the visual am not sure how to solve it. I really appreciate it, Thank you very much!

Like

I have found some issues with running the script against files in OneDrive. It works fine on the same files in a local folder. Any Ideas anyone?

Like
Charles Mathieu
Charles Mathieu
Mar 09, 2023
Replying to

I'm facing the same issue when pointing to a OneDrive path. Have you resolved your issue? I'm getting an error at line 137: File does not exist. Must use a valid .pbix or .pbit file.


Not facing this issue when moving the file to a local folder.

Like

Hey, Really interested in the UnusedObject.txt specified in the Readme.md of your repo. https://github.com/m-kovalsky/Tabular#export-report-objects I don't see that functionality in the output of this script. I might be stupid, is it here? Cheers

Like
Replying to

Hi Evan, you must be connected to the model file within Tabular Editor & set the addPersp parameter to true.

Like

Jakub Tamas
Jakub Tamas
Nov 19, 2021

This is incredibly powerful, thanks for sharing; Question: is it possible to get any more info about Bookmarks? They are quite a nuisance on big projects and it is very frustrating to try to find out which options they are using or which visuals they refer to (when not using "All Visuals").

Like
Replying to

Thanks Jakub. More info on bookmarks is coming soon. Note that the primary place for this going forward will be via the Report Analyzer.

Like
bottom of page