Forum Discussion

GokulanRajan's avatar
GokulanRajan
Frequent Visitor
2 years ago

Extracting Dataflow and Dataset fields & Attributes from PBIX

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...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
GokulanRajan's avatar
GokulanRajan
Frequent Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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"