Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Is there a way to export a list of source data tables from a dataset PBIX so that I can easily share it with my dba, rather than manually trawling through power query in every dataset?
I was somehow lost after 'Save Excel' and also how to properly upload it in PBI. Also unsure where to plug in PQ.
@Anonymous ,
Refer this :https://community.powerbi.com/t5/Desktop/Table-List-and-Field-List/td-p/518693
Not sure on this. But see if REST APIs can help https://docs.microsoft.com/en-us/rest/api/power-bi/
Thanks for the reply
I've tried and don't think you can access information about the source tables in a dataset via the API - just the source database
Hi @Anonymous ,
You can refer to the following blogs and use DAX Studio to get the list of tables used in the report dataset.
Extract Table Size from Power BI Desktop Using DAX Studio
Capturing Power BI queries using DAX Studio
Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
Thanks but neither are relevant to the question
I think it's actually impossible to export a list of source tables (e.g. from a data warehouse) from a PBIX file
Hi @Anonymous ,
Please check whether the one in the below screenshot is what you want. If no, you can raise a new idea and add your comments there to make this feature coming sooner.
select * from $SYSTEM.TMSCHEMA_PARTITIONS
Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
Thanks @v-yiruan-msft - this is helpful. I was able to further use Power Query to clean the outputs to of this command to give exactly what I needed. Sharing here in case other find it helpful.
STEPS
Step 1: Connect the Power BI to DAX Studio
Step 2: Change the output to Excel file from Home ribbon in DAX studio
Step 3: Run the following query as suggested by @v-yiruan-msft above and save the excel
select * from $SYSTEM.TMSCHEMA_PARTITIONS
Step 4: Open the save excel. Make the data into an excel table and load it in PowerQuery
Step 5: Use the following PQ script to get clean list of all queries and data sources
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
RemovedOtherColumns = Table.SelectColumns(Source,{"Name", "QueryDefinition"}),
FilteredDateTablesRowsOut = Table.SelectRows(RemovedOtherColumns, each not Text.Contains([Name], "DateTable")),
#"Extracted Text Before Delimiter" = Table.TransformColumns(FilteredDateTablesRowsOut, {{"Name", each Text.BeforeDelimiter(_, "-"), type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted Text Before Delimiter", {{"QueryDefinition", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "QueryDefinition"),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"QueryDefinition", Text.Trim, type text}}),
#"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each Text.StartsWith([QueryDefinition], "Source")),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"QueryDefinition", each Text.AfterDelimiter(_, "Source = "), type text}}),
#"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"QueryDefinition", each Text.BeforeDelimiter(_, ",", {0, RelativePosition.FromEnd}), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter1",{{"QueryDefinition", "Source"}, {"Name", "Query Name"}})
in
#"Renamed Columns"
Cheers,
B
Extended the PQ script to give a bit more details about the source files type, name, and sharepoint site names where they are stored.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
RemovedOtherColumns = Table.SelectColumns(Source,{"Name", "QueryDefinition"}),
FilteredDateTablesRowsOut = Table.SelectRows(RemovedOtherColumns, each not Text.Contains([Name], "DateTable")),
#"Extracted Text Before Delimiter" = Table.TransformColumns(FilteredDateTablesRowsOut, {{"Name", each Text.BeforeDelimiter(_, "-"), type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted Text Before Delimiter", {{"QueryDefinition", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "QueryDefinition"),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"QueryDefinition", Text.Trim, type text}}),
#"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each Text.StartsWith([QueryDefinition], "Source")),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"QueryDefinition", each Text.AfterDelimiter(_, "Source = "), type text}}),
#"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"QueryDefinition", each Text.BeforeDelimiter(_, ",", {0, RelativePosition.FromEnd}), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter1",{{"QueryDefinition", "Source"}, {"Name", "Query Name"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Source Type", each if Text.Contains([Source], "Csv") then "CSV" else if Text.Contains([Source], "Excel") then "Excel" else "Others"),
GetCSVFromWebPaths = Table.AddColumn(#"Added Conditional Column", "CSV From Web Path", each Text.BetweenDelimiters([Source], "Csv.Document(Web.Contents(", ")"), type text),
GetExcelFromWebPaths = Table.AddColumn(GetCSVFromWebPaths, "Excel From Web Path", each Text.BetweenDelimiters([Source], "Excel.Workbook(Web.Contents(", ")") & Text.BetweenDelimiters([Source], "Excel.Workbook(File.Contents(", ")"), type text),
#"Merged Columns" = Table.CombineColumns(GetExcelFromWebPaths,{"CSV From Web Path", "Excel From Web Path"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"File Path"),
#"Duplicated Column" = Table.DuplicateColumn(#"Merged Columns", "File Path", "FilePathCopy"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","\","/",Replacer.ReplaceText,{"FilePathCopy"}),
GetFileName = Table.AddColumn(#"Replaced Value", "File Name", each Text.AfterDelimiter([FilePathCopy], "/", {0, RelativePosition.FromEnd}), type text),
GetSPSiteName = Table.TransformColumns(GetFileName, {{"FilePathCopy", each Text.BetweenDelimiters(_, "sites/", "/"), type text}}),
#"Renamed Columns1" = Table.RenameColumns(GetSPSiteName,{{"FilePathCopy", "SharePoint Site Name"}})
in
#"Renamed Columns1"
Cheers,
B
Extended further to include dependency path for simple Query References (not Table.Combine like queries)
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
RemovedOtherColumns = Table.SelectColumns(Source,{"Name", "QueryDefinition"}),
FilteredDateTablesRowsOut = Table.SelectRows(RemovedOtherColumns, each not Text.Contains([Name], "DateTable")),
#"Extracted Text Before Delimiter" = Table.TransformColumns(FilteredDateTablesRowsOut, {{"Name", each Text.BeforeDelimiter(_, "-"), type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted Text Before Delimiter", {{"QueryDefinition", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "QueryDefinition"),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"QueryDefinition", Text.Trim, type text}}),
#"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each Text.StartsWith([QueryDefinition], "Source")),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"QueryDefinition", each Text.AfterDelimiter(_, "Source = "), type text}}),
#"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"QueryDefinition", each Text.BeforeDelimiter(_, ",", {0, RelativePosition.FromEnd}), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter1",{{"QueryDefinition", "Source"}, {"Name", "Query Name"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Source Type", each if Text.Contains([Source], "Csv") then "CSV" else if Text.Contains([Source], "Excel") then "Excel" else if Text.Contains([Source], "Json") then "JSON String" else if Text.Contains([Source], "SharePoint.Tables") then "SharePoint Table" else "Others"),
fxLookup = (QueryName, TableOfQueries) =>
let
CleanName = Text.Remove( QueryName , {"#", """"}),
Result = try TableOfQueries{[#"Query Name" = CleanName]}[Source] otherwise null
in
Result,
GetNestedQueriesPath =
let
myQueries = Table.Buffer(#"Added Conditional Column"),
fxIterativeLookup = (QueryName, TableOfQueries) =>
let
fxSource = List.Generate(
()=> QueryName ,
each fxLookup( _ , TableOfQueries) <> null ,
each fxLookup( _ , TableOfQueries)
)
in
fxSource
in
Table.AddColumn(#"Added Conditional Column", "Query Dependency Path", each if [Source Type] = "Others" then fxIterativeLookup([Source], myQueries ) else null),
GetRootSource = let previousStep = Table.Buffer(GetNestedQueriesPath) in Table.AddColumn( previousStep , "Root Source", each if [Source Type] = "Others" then fxLookup( List.Last([Query Dependency Path]), previousStep) else [Source]),
#"Extracted Values" = Table.TransformColumns(GetRootSource, {"Query Dependency Path", each try Text.Combine(List.Transform(_, Text.From), " >> ") otherwise null, type text}),
CleanedQueryPath = Table.TransformColumns(#"Extracted Values",{{"Query Dependency Path", each Text.Remove( _ , {"#",""""}) , type text}}),
ReplacedEmptyWithNull = Table.ReplaceValue(CleanedQueryPath,"",null,Replacer.ReplaceValue,{"Query Dependency Path"}),
CategorizeReferenceQueries = Table.ReplaceValue(ReplacedEmptyWithNull,"Others",each if [Query Dependency Path] <> null then "Query Reference" else "Others" ,Replacer.ReplaceText,{"Source Type"}),
GetCSVFromWebPaths = Table.AddColumn(CategorizeReferenceQueries, "CSV From Web Path", each Text.BetweenDelimiters([Root Source], "Csv.Document(Web.Contents(", ")"), type text),
GetExcelFromWebPaths = Table.AddColumn(GetCSVFromWebPaths, "Excel From Web Path", each Text.BetweenDelimiters([Root Source], "Excel.Workbook(Web.Contents(", ")") & Text.BetweenDelimiters([Source], "Excel.Workbook(File.Contents(", ")"), type text),
#"Merged Columns" = Table.CombineColumns(GetExcelFromWebPaths,{"CSV From Web Path", "Excel From Web Path"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"File Path"),
#"Duplicated Column" = Table.DuplicateColumn(#"Merged Columns", "File Path", "FilePathCopy"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","\","/",Replacer.ReplaceText,{"FilePathCopy"}),
GetFileName = Table.AddColumn(#"Replaced Value", "File Name", each Text.AfterDelimiter([FilePathCopy], "/", {0, RelativePosition.FromEnd}), type text),
#"Replaced Value1" = Table.ReplaceValue(GetFileName,"%20"," ",Replacer.ReplaceText,{"File Name"}),
GetSPSiteName = Table.TransformColumns(#"Replaced Value1", {{"FilePathCopy", each Text.BetweenDelimiters(_, "sites/", "/"), type text}}),
#"Renamed Columns1" = Table.RenameColumns(GetSPSiteName,{{"FilePathCopy", "SharePoint Site Name"}}),
AddedSourceSummary = Table.AddColumn(#"Renamed Columns1", "Root Source Summary", each if [File Name] <> "" then [File Name] & "#(lf)SP Site: " & [SharePoint Site Name] else null),
#"Reordered Columns" = Table.ReorderColumns(AddedSourceSummary,{"Query Name", "Source Type", "Query Dependency Path", "Root Source Summary", "Source", "Root Source", "File Path", "SharePoint Site Name", "File Name"})
in
#"Reordered Columns"
Hi - Do you have a step by step instructions on how to make this work?
Is this not what you are looking for?
Re: Export a list of data source TABLES from a dat... - Microsoft Power BI Community
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |