Power BI Custom Connector: Build a Module's Lineage View

What

The scenario we are dealing with is as follows:

Power Query connectors do not support Lineage View in Visual Studio Code, unlike in Power BI. I have undertaken the task of restructuring an old project: a massive .pb file comprising 2091 lines.

WHY

In Power BI custom connector projects, comprehending the data flow from the data source to its destination can pose a challenge:

My new project consists of more than 100 modules, and these modules are interconnected using Extention.LoadFunction.

• Questions like "What happens if I change this module?", "How many modules I already create?", "Which module did not use any more? ", "Why this module did not work? " can be hard to answer.

HOW

I would like a clear visual representation of the flow. For instance, ABCProject is linked to module.02_main, and module.02_main is connected to module.05_navigator, and so forth.

To begin, I must generate metadata within the code to establish the connections between modules. I will utilize the comments //Lineage and //EndLineage as reference markers, implementing this for each .pb and .pqm file.

...
//Lineage
? ? module.05_navigater = Extension.LoadFunction("module.05_navigater.pqm"),
//EndLineage
...        

... invokes the Power BI query to retrieve the file's metadata. I have built a function for code parsing. ?The following M code:

let
? ? Source = (Parameter1 as binary) => let
? ? Source = Csv.Document(Parameter1,[Delimiter=";", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None]),
? ? #"Trimmed Text" = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}),
? ? #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1", Text.Clean, type text}}),
? ? #"Added Index" = Table.AddIndexColumn(#"Cleaned Text", "Index", 0, 1, Int64.Type),
? ? FilteredHeaderRows = Table.SelectRows(#"Added Index", each ([Column1] = "https://Lineage")),
? ? FilteredBottomRows = Table.SelectRows(#"Added Index", each ([Column1] = "https://EndLineage")),
? ? IsRef = Table.RowCount(FilteredHeaderRows) > 0,
? ? Top = FilteredHeaderRows{0}[Index],
? ? Bot = FilteredBottomRows{0}[Index],
? ? #"Kept Range of Rows" = Table.Range(#"Added Index",Top+1,Bot-Top-1),
? ? #"Removed Other Columns" = Table.SelectColumns(#"Kept Range of Rows",{"Column1"}),
? ? #"Inserted Text Between Delimiters" = Table.AddColumn(#"Removed Other Columns", "Text Between Delimiters", each Text.BetweenDelimiters([Column1], """", """"), type text),
? ? #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", "Node"}}),
? ? #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns",{"Node"}),
? ? result = if IsRef then #"Removed Other Columns1" else #table({"Node"}, {{""}})
in
? ? result
in
? ? Source        

...utilizes the function to obtain the linkage among files. You'll invoke the custom function on the files within the custom PB project using Folder.Files(..). Here is the resulting output:

I need the flattening of hierarchies with multiple parents and children. Using the code from www.thebiccountant.com/ makes this process quite straightforward. Here is the resulting output:

Add columns for Level 1, Level 2, and so on, to the Decomposite Tree. Here's the configuration of the visual in Power BI:

However, we can achieve an even better outcome.?In the example pbix file I’ve uploaded?here, I’ve created a function to get the content of each files. I won’t go through the M code because there’s nothing interesting in it, but the end result is a report that allows you to drill through to the file's content:

One last thing to point out regarding these examples is that the Decomposite Tree is not build for Lineview. It will not display accurately if a module is utilized by many other modules.