Database Tables for all datasets across all workspaces

[Ynr0225]

Hi All,

Using Power BI Scanner Rest API's i extracted the metadata in Json format. Later i used that json file as a data source in Power Bi desktop and created a table visual with workspace, datasets,power query expressions. If i export it into csv file, the power query expressions column is contuinueing to other columns due to character length as shown in below screenshot

My main goal is i want to export it to csv file and share to higher officials.
If ipossible let me know how to get server,database,schema and table in power bi desktop based on Power query expressions. Please share me the power query to get those details. I am giving the power query script also
let
Source = Json.Document(File.Contents("Path\PBI_API_output.json")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"workspaces"}, {"workspaces"}),
#"Expanded workspaces" = Table.ExpandListColumn(#"Expanded Column1", "workspaces"),
#"Expanded workspaces1" = Table.ExpandRecordColumn(#"Expanded workspaces", "workspaces", {"id", "name", "description", "type", "state", "isOnDedicatedCapacity", "capacityId", "defaultDatasetStorageFormat", "reports", "dashboards", "datasets"}, {"workspaces.id", "workspaces.name", "workspaces.description", "workspaces.type", "workspaces.state", "workspaces.isOnDedicatedCapacity", "workspaces.capacityId", "workspaces.defaultDatasetStorageFormat", "workspaces.reports", "workspaces.dashboards", "workspaces.datasets"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded workspaces1",{{"workspaces.id", type text}, {"workspaces.name", type text}, {"workspaces.description", type text}, {"workspaces.type", type text}, {"workspaces.state", type text}, {"workspaces.isOnDedicatedCapacity", type logical}, {"workspaces.capacityId", type text}, {"workspaces.defaultDatasetStorageFormat", type text}, {"workspaces.reports", type any}, {"workspaces.dashboards", type any}, {"workspaces.datasets", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"workspaces.description", "workspaces.type", "workspaces.state", "workspaces.isOnDedicatedCapacity", "workspaces.capacityId", "workspaces.defaultDatasetStorageFormat", "workspaces.reports", "workspaces.dashboards"}),
#"Expanded workspaces.datasets" = Table.ExpandListColumn(#"Removed Columns", "workspaces.datasets"),
#"Expanded workspaces.datasets1" = Table.ExpandRecordColumn(#"Expanded workspaces.datasets", "workspaces.datasets", {"id", "name", "tables", "expressions", "configuredBy", "refreshSchedule"}, {"workspaces.datasets.id", "workspaces.datasets.name", "workspaces.datasets.tables", "workspaces.datasets.expressions", "workspaces.datasets.configuredBy", "workspaces.datasets.refreshSchedule"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded workspaces.datasets1",{{"workspaces.name", "Workspace"}, {"workspaces.datasets.name", "Dataset"}}),
#"Expanded workspaces.datasets.tables" = Table.ExpandListColumn(#"Renamed Columns", "workspaces.datasets.tables"),
#"Expanded workspaces.datasets.tables1" = Table.ExpandRecordColumn(#"Expanded workspaces.datasets.tables", "workspaces.datasets.tables", {"name", "source"}, {"workspaces.datasets.tables.name", "workspaces.datasets.tables.source"}),
#"Expanded workspaces.datasets.tables.source" = Table.ExpandListColumn(#"Expanded workspaces.datasets.tables1", "workspaces.datasets.tables.source"),
#"Expanded workspaces.datasets.tables.source1" = Table.ExpandRecordColumn(#"Expanded workspaces.datasets.tables.source", "workspaces.datasets.tables.source", {"expression"}, {"workspaces.datasets.tables.source.expression"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded workspaces.datasets.tables.source1",{"workspaces.datasets.expressions"}),
#"Expanded workspaces.datasets.refreshSchedule" = Table.ExpandRecordColumn(#"Removed Columns1", "workspaces.datasets.refreshSchedule", {"days", "times", "enabled", "localTimeZoneId"}, {"workspaces.datasets.refreshSchedule.days", "workspaces.datasets.refreshSchedule.times", "workspaces.datasets.refreshSchedule.enabled", "workspaces.datasets.refreshSchedule.localTimeZoneId"}),
#"Expanded workspaces.datasets.refreshSchedule.days" = Table.ExpandListColumn(#"Expanded workspaces.datasets.refreshSchedule", "workspaces.datasets.refreshSchedule.days"),
#"Expanded workspaces.datasets.refreshSchedule.times" = Table.ExpandListColumn(#"Expanded workspaces.datasets.refreshSchedule.days", "workspaces.datasets.refreshSchedule.times"),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded workspaces.datasets.refreshSchedule.times",{
{"workspaces.id", "Workspace_ID"},
{"Workspace", "Workspace_Name"},
{"workspaces.datasets.id", "Dataset_ID"},
{"Dataset", "Dataset_Name"},
{"workspaces.datasets.tables.name", "Datasets_Tables_Name"},
{"workspaces.datasets.tables.source.expression", "Datasets_Tables_Source_Expression"},
{"workspaces.datasets.configuredBy", "Dataset_Owner"},
{"workspaces.datasets.refreshSchedule.days", "Days"},
{"workspaces.datasets.refreshSchedule.times", "Times"},
{"workspaces.datasets.refreshSchedule.enabled", "Refresh_Schedule_enable"},
{"workspaces.datasets.refreshSchedule.localTimeZoneId", "Local_TimeZone"}
}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each [Workspace_ID] <> "4b62db7a-05b4-404f-a653-952b00af703c"),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Dataset_Name] <> "Usage Metrics Report" and [Dataset_Name] <> "Dashboard Usage Metrics Model" and [Dataset_Name] <> "Report Usage Metrics Model"),
// Group by all columns except Days and Times, aggregate Days and Times
#"Grouped Rows" = Table.Group(#"Filtered Rows1",
{"Workspace_ID", "Workspace_Name", "Dataset_ID", "Dataset_Name", "Datasets_Tables_Name", "Datasets_Tables_Source_Expression", "Dataset_Owner", "Refresh_Schedule_enable", "Local_TimeZone"},
{
{"Days", each Text.Combine(List.Sort(List.Distinct([Days])), ","), type text},
{"Times", each Text.Combine(List.Distinct([Times]), ","), type text}
}
),
#"Filtered Rows2" = Table.SelectRows(#"Grouped Rows", each ([Dataset_Name] <> null))
in
#"Filtered Rows2"

[v-aatheeque]

Hi @Ynr0225 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[lbendlin]

Load the JSON into a database or data warehouse that can handle them natively.  Then connect your Power BI to that data source. That way you can eliminate the "Export to CSV" step.

[Ynr0225]

Hello @lbendlin ,

Thanks for responding higher officials wants it in csv file. We don't have any database or warehouse. Actually i want database tables and schema used in Power BI.
If anyone knows how to get the database tables and schema from Power Query Expressions. please let me know.

[v-aatheeque]

Hi @Ynr0225 

Thanks for clarifying your requirement. The Power BI Scanner API JSON doesn’t directly expose server, database, schema, or table names as separate fields. Those details are embedded inside the Power Query expressions column.

[Ynr0225]

Hi @v-aatheeque ,

Thanks for responding. Do you know how to get the database tables from Power Query expressions column. 

[lbendlin]

Tables are not expressions, they are partitions.

[v-aatheeque]

Hi @Ynr0225 

Just checking in to see if the clarification helped.
As mentioned earlier by @lbendlin , tables themselves won’t appear directly in the Power Query expressions column since those expressions represent query logic, whereas tables are stored as partitions within the dataset.