How to Manually Insert Power Query Script

Note: This workaround is NOT needed when using Azure extraction mode with access to Scanner API set up correctly. In such cases, all Power Query scripts are extracted automatically.

As of Power BI version 2.82.5858.602 64-bit (June 2020), the Power Query script is encrypted and inaccessible by IBM Manta Data Lineage. As a result, it is necessary to manually insert these scripts.

Note: The issue of encrypted Power Query scripts is encountered during the dataflow analysis of the extracted Power BI report. The warning message “PowerQuery is not included in the report“ will be logged during dataflow analysis of the report.

Scanning Power Query when using Power BI on-premises
- Extract Power Query using Power BI Desktop
  - Download Power BI Desktop from Microsoft
    https://powerbi.microsoft.com/en-us/desktop/
- Power BI reports (.PBIX) files do not expose the Power Query scripts that are needed to build the lineage back to data sources. The on-premises Power BI server also does not support the same functionality as in Power BI Service hosted in Azure. To overcome this limitation of Power BI on-premises, Power Query must be extracted from the PBIX files and placed into the Manta CLI Input directory. The following steps detail how this is accomplished.
  - Here we have an example report that has been extracted by Manta Data Lineage from an on-premises Power BI Server.
  - This report is extracted to the [mantaflow]/cli/temp/powerbi/[connectionName] directory.
  - To extract the Power Query from this report, we must open the report in Power BI Desktop.
  - Next, click the Transform Data button in the toolbar, select all (using Ctrl+LeftClick) on each of the sources in the Queries area on the left, and press Ctrl+C to copy all the queries.
  - Then, open a text file editor, such as notepad or something similar, and paste the results into the file.
  - Here, we have a single Power Query. However, your report may have many entries.
  - Some edits might be necessary if the query was created manually, not generated by Power BI.
    Check this list before copying the queries to the .txt file.
    - Remove all comments apart from one very important comment line: the name of the table on the line preceding the line with the keyword let.
    - Place every let and in on a separate line, as shown in the following example.
    The result should look something like the following example. There may be more lines in the let section and more sections for individual PowerQueries.
```
// Source
let
    Source = Text.Proper("hello world") 
in 
    Source
```
Save this text file in the folder [mantaflow]/cli/input/powerbi/[connectionName].
The location where these text files are saved is a mirror to the location where the report was saved during extraction. For example, if the report was extracted to:
[mantaflow]/cli/temp/powerbi/[connectionName]/corporateReports/HR/report.pbix
[mantaflow]/cli/input/powerbi/[connectionName]/corporateReports/HR/report.txt
Once all Power Query files have been created, run the workflow to analyze the Power BI connection.