Record.AllQueries.pq

let func =   
 (optional SelectedType as type, optional ExcludeList as list) =>
let

/* Debug Parameters
    SelectedType = type table,
    ExcludeList = null,
End of debug parameters */
    
    //By default, the query "GetAllQueries" will be excluded, so if you name this query so, you get everything BUT this query.
    excludeList = if ExcludeList = null then {"GetAllQueries"} else ExcludeList,
    ExcludeTable = Table.FromList(excludeList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Source = #shared,
    ToTable = Record.ToTable(Source),
    
    // "GoogleAnalytics.Accounts is currently the first inbuilt function that appears.
    // This might change in the future and would have to be adjusted then.
    FindFirstNativeFunction = List.PositionOf(ToTable[Name], "GoogleAnalytics.Accounts"),
    
    SelectOnlyOwnQueries = Table.FirstN(ToTable, FindFirstNativeFunction-1),
    FilterOutExcludeQueries = try Table.NestedJoin(SelectOnlyOwnQueries,{"Name"},ExcludeTable,{"Column1"},"SelectOnlyOwnQueries",JoinKind.LeftAnti) otherwise SelectOnlyOwnQueries,
    RemoveErrors = Table.RemoveRowsWithErrors(FilterOutExcludeQueries, {"Value"}),
    FilterSelectedTypes = Table.SelectRows(RemoveErrors, each Value.Is([Value], SelectedType))[Name],
    ReturnSelection = if SelectedType = null then RemoveErrors[Name] else FilterSelectedTypes,
    Combiners = if ExcludeList = null then """, """ else """, #""",
    Text = "{"""&Text.Combine(ReturnSelection, """, """)&"""}",
    List = "{#"""&Text.Combine(ReturnSelection, """, #""")&"""}",
    FullTable = "Table.FromColumns({"& Text &", "& List &"}, {""QueryName"", ""Query""})",
    FormatsRecord = [Text = Text, List = List, Table = FullTable]
in
    FormatsRecord ,
documentation = [
Documentation.Name =  " Record.AllQueries ",
Documentation.Description = " Creates a record with different sorts of references to the existing queries in the query pane. ",
Documentation.LongDescription = " Creates a record with different sorts of references to the existing queries in the query pane. Can only be used in the query editor, as shared will not work when loaded to the data model. Optional <code>SelectedType</code> can be used to filter a specific return type and optional <code>ExcludeList</code> to exclude certain queries from the result. ",
Documentation.Category = " Record ",
Documentation.Source = " local ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com . ",
Documentation.Examples = {[Description =  "  ",
Code = "  ",
Result = "  "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))