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"
