Extracting Dataflow and Dataset fields & Attributes from PBIX

GokulanRajan — Thu, 09 Nov 2023 13:25:17 GMT

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.

Re: Extracting Dataflow and Dataset fields & Attributes from PBIX

lbendlin — Mon, 13 Nov 2023 18:44:18 GMT

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

Re: Extracting Dataflow and Dataset fields & Attributes from PBIX

Anonymous — Tue, 14 Nov 2023 02:03:08 GMT

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

Re: Extracting Dataflow and Dataset fields & Attributes from PBIX

GokulanRajan — Thu, 16 Nov 2023 11:46:00 GMT

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

Re: Extracting Dataflow and Dataset fields & Attributes from PBIX

lbendlin — Thu, 16 Nov 2023 13:53:21 GMT

You get the M scripts via DMVs.

Re: Extracting Dataflow and Dataset fields & Attributes from PBIX

Anonymous — Mon, 20 Nov 2023 03:06:47 GMT

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"

topic Re: Extracting Dataflow and Dataset fields & Attributes from PBIX in Developer

Extracting Dataflow and Dataset fields & Attributes from PBIX

Re: Extracting Dataflow and Dataset fields & Attributes from PBIX

Re: Extracting Dataflow and Dataset fields & Attributes from PBIX

Re: Extracting Dataflow and Dataset fields & Attributes from PBIX

Re: Extracting Dataflow and Dataset fields & Attributes from PBIX

Re: Extracting Dataflow and Dataset fields & Attributes from PBIX