Forum Discussion
Some13lse
2 years agoFrequent Visitor
Report about dataflow content - unable to get columns
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?