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 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.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

  • 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"