Extracting Dataflow and Dataset fields & Attributes from PBIX

[GokulanRajan]

Hi Team,

We are using Layout file from Reports pbix to extract reports, pages and visuals. But we are not able to extract dataset and dataflow information from layout is there any other file like DataModel which contains the dataset fields and attributes information?

Also not able to view contents of Datamodel file extracted from PBIX

Can you please help in understanding how we can get 

Dataset fields,

Dataflow fields,

Mapping between a Report attribute & a Dataset field or attribute.

[GokulanRajan]

I am already using Scanner API to get dataset fields and reports etc

But we don't get Report fields in Scanner.

We also tried PBIX extraction we don't get mapping information between a report field and dataset field.

Any help with usage of Power Query M script

@Anonymous - My personal preference is ADF + ASQL Extracting Power BI metadata with Data Factory (part 1) – justB smart. this again doesn't give report fields

[Anonymous]

Hi GokulanRajan,

If PBI Explorer doesn't help, I think you're more or less starting from scratch. Report.json is not documented officially nor unofficially from what I can gather. However, I ran a small test this morning and was able to map fields to visual and to page. Didn't spend much time thinking about optimisation and this was on a very basic dataset of one table, one column, one measure, one page and one visualisation but if it's important to you, maybe expand the test. Resulting table using Power Query:

Note that report.json does distinguish between column and measure but I didn't incorporate this into my test. This would be relevant if when mapping Table.Field back to GetScanResult/Datasets/Tables/Columns and .../Measures, in the case where you have Columns and Measures of the same name.

Path for each of the fields above, left to right:

sections.displayName

sections.visualContainers.config.singleVisual.prototypeQuery.Select.Name

sections.visualContainers.config.singleVisual.vcObjects.title.properties.text..2

Query:

let
Source = report, // this is report.json file
#"Removed Other Columns" = Table.SelectColumns(Source,{"sections"}),
#"Expanded sections" = Table.ExpandListColumn(#"Removed Other Columns", "sections"),
#"Expanded sections1" = Table.ExpandRecordColumn(#"Expanded sections", "sections", {"displayName", "visualContainers"}, {"sections.displayName", "sections.visualContainers"}),
#"Expanded sections.visualContainers" = Table.ExpandListColumn(#"Expanded sections1", "sections.visualContainers"),
#"Expanded sections.visualContainers1" = Table.ExpandRecordColumn(#"Expanded sections.visualContainers", "sections.visualContainers", {"config"}, {"sections.visualContainers.config"}),
#"Parsed JSON" = Table.TransformColumns(#"Expanded sections.visualContainers1",{{"sections.visualContainers.config", Json.Document}}),
#"Expanded sections.visualContainers.config" = Table.ExpandRecordColumn(#"Parsed JSON", "sections.visualContainers.config", {"singleVisual"}, {"sections.visualContainers.config.singleVisual"}),
#"Expanded sections.visualContainers.config.singleVisual" = Table.ExpandRecordColumn(#"Expanded sections.visualContainers.config", "sections.visualContainers.config.singleVisual", {"prototypeQuery", "vcObjects"}, {"sections.visualContainers.config.singleVisual.prototypeQuery", "sections.visualContainers.config.singleVisual.vcObjects"}),
#"Expanded sections.visualContainers.config.singleVisual.prototypeQuery" = Table.ExpandRecordColumn(#"Expanded sections.visualContainers.config.singleVisual", "sections.visualContainers.config.singleVisual.prototypeQuery", {"Select"}, {"sections.visualContainers.config.singleVisual.prototypeQuery.Select"}),
#"Expanded sections.visualContainers.config.singleVisual.prototypeQuery.Select" = Table.ExpandListColumn(#"Expanded sections.visualContainers.config.singleVisual.prototypeQuery", "sections.visualContainers.config.singleVisual.prototypeQuery.Select"),
#"Expanded sections.visualContainers.config.singleVisual.prototypeQuery.Select1" = Table.ExpandRecordColumn(#"Expanded sections.visualContainers.config.singleVisual.prototypeQuery.Select", "sections.visualContainers.config.singleVisual.prototypeQuery.Select", {"Name"}, {"sections.visualContainers.config.singleVisual.prototypeQuery.Select.Name"}),
#"Expanded sections.visualContainers.config.singleVisual.vcObjects" = Table.ExpandRecordColumn(#"Expanded sections.visualContainers.config.singleVisual.prototypeQuery.Select1", "sections.visualContainers.config.singleVisual.vcObjects", {"title"}, {"sections.visualContainers.config.singleVisual.vcObjects.title"}),
#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title" = Table.ExpandListColumn(#"Expanded sections.visualContainers.config.singleVisual.vcObjects", "sections.visualContainers.config.singleVisual.vcObjects.title"),
#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title1" = Table.ExpandRecordColumn(#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title", "sections.visualContainers.config.singleVisual.vcObjects.title", {"properties"}, {"sections.visualContainers.config.singleVisual.vcObjects.title.properties"}),
#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title.properties" = Table.ExpandRecordColumn(#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title1", "sections.visualContainers.config.singleVisual.vcObjects.title.properties", {"text"}, {"sections.visualContainers.config.singleVisual.vcObjects.title.properties.text"}),
#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title.properties.text" = Table.ExpandRecordColumn(#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title.properties", "sections.visualContainers.config.singleVisual.vcObjects.title.properties.text", {"expr"}, {"sections.visualContainers.config.singleVisual.vcObjects.title.properties.text.ex"}),
#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title.properties.text.ex" = Table.ExpandRecordColumn(#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title.properties.text", "sections.visualContainers.config.singleVisual.vcObjects.title.properties.text.ex", {"Literal"}, {"sections.visualContainers.config.singleVisual.vcObjects.title.properties.text..1"}),
#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title.properties.text..1" = Table.ExpandRecordColumn(#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title.properties.text.ex", "sections.visualContainers.config.singleVisual.vcObjects.title.properties.text..1", {"Value"}, {"sections.visualContainers.config.singleVisual.vcObjects.title.properties.text..2"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded sections.visualContainers.config.singleVisual.vcObjects.title.properties.text..1",{"sections.displayName", "sections.visualContainers.config.singleVisual.vcObjects.title.properties.text..2", "sections.visualContainers.config.singleVisual.prototypeQuery.Select.Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"sections.displayName", "Page Name"}, {"sections.visualContainers.config.singleVisual.vcObjects.title.properties.text..2", "Visual Title"}, {"sections.visualContainers.config.singleVisual.prototypeQuery.Select.Name", "Table.Field"}})
in
#"Renamed Columns"

[lbendlin]

You get the M scripts via DMVs.

[Anonymous]

If you want tenant level, Admin APIs

Power BI REST API Connector — The Power User connector for PBI, this is most straightforward but no persistent storage.

My personal preference is ADF + ASQL Extracting Power BI metadata with Data Factory (part 1) – justB smart

If PowerShell is your flavour, take a look here: GitHub - RuiRomano/pbimonitor

If you are looking to map visual objects to dataset columns/measures, maybe PBI Explorer

[lbendlin]

Read about the PBIP format.  Read about DMVs. Read about the Scanner API.