Report about dataflow content - unable to get columns

[Some13lse]

Greetings all,

My target is to build a report about the dataflows on our Power BI service.
The reports should show the workspaces, Dataflows, the tables and columns.
So far I was able to create a power query to obtain the data up till the table level.

// DataflowSource
let
    Source = PowerPlatform.Dataflows(null),
    Workspaces1 = Source{[Id="Workspaces"]}[Data],
    #"Removed Workspace Columns" = Table.RemoveColumns(Workspaces1,{"workspaceType", "Tags", "ItemKind", "ItemName", "IsLeaf"}),
    #"Renamed Workspace Columns" = Table.RenameColumns(#"Removed Workspace Columns",{{"workspaceId", "WorkspaceId"}, {"workspaceName", "WorkspaceName"}}),
    #"Expanded Dataflows" = Table.ExpandTableColumn(#"Renamed Workspace Columns", "Data", {"dataflowId", "dataflowName", "description", "Data"}, {"dataflowId", "dataflowName", "description", "Data.1"}),
    #"Renamed Dataflow Columns" = Table.RenameColumns(#"Expanded Dataflows",{{"description", "DataflowDescription"}, {"Data.1", "Tables"}, {"dataflowName", "DataflowName"}, {"dataflowId", "DataflowId"}}),
    #"Expanded Tables" = Table.ExpandTableColumn(#"Renamed Dataflow Columns", "Tables", {"Data", "entityName"}, {"Data", "entityName"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Tables", "Index", 1, 1, Int64.Type),
    #"Renamed Table Columns" = Table.RenameColumns(#"Added Index",{{"entityName", "TableName"}, {"Data", "Columns"}, {"Index", "TableId"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Table Columns",{"WorkspaceId", "WorkspaceName", "DataflowId", "DataflowName", "DataflowDescription", "TableId", "TableName", "Columns"}),
    #"Expanded Columns" = Table.ExpandTableColumn(#"Reordered Columns", "Columns", {"sd_aanbieder_KEY", "sd_aanbieder_bk", "Code aanbieder"}, {"Columns.sd_aanbieder_KEY", "Columns.sd_aanbieder_bk", "Columns.Code aanbieder"})
in
    #"Expanded Columns"

I Strugle to obtain the Column level.
The structure per table will differ and to use Table.ExpandTableColumn you need give a list of the columns.
Something like the following step needs to be done so the result of the query will contain a row for every column of every table.

let
    Source = DataFlowSource,
    Columns = Source{0}[Columns],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Columns, {}, "ColumnName", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"ColumnName"}, {{"NumberOfRows", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 
How can this be done?

[Some13lse]

Thanks for your reply @lbendlin ,

I was not aware of the Scanner API and have no experience in using Power BI REST APIs.
We are not on Fabric and it is not clear yet if we will be in the near future, so therefore not used Purview.

[lbendlin]

Purview works with legacy Power BI too.

[lbendlin]

Any particular reason for not using the Scanner API or Purview for this?