none
Enumerate a List of Queries RRS feed

  • Question

  • Hi guys

    Does anyone know of a way to enumerate a list of queries within the current or target workbook/PBI model?

    My goal is to make the below script dynamic so I don't have to explicitly refer to a query by name:

    let

    ModelProfile = Table.Combine
    (
        {
            Table.AddColumn(Table.Profile(fctData), "TableName", each "fctData"), 
            Table.AddColumn(Table.Profile(dimData), "TableName", each "dimData")
        }
    )

    in
        ModelProfile



    • Edited by Simon N Monday, October 17, 2016 4:45 PM
    Monday, October 17, 2016 4:44 PM

Answers

  • Thanks guys

    At the end of the day I don't think there's a way to do it without referencing the default #shared list. This would work of course, but it would require maintenance with every PQ update and therefore defeat the purpose of creating the audit script.

    Great idea with the naming convention idea Ehren.  However, my goal with this script is to audit other peoples models so unfortunately that won't work in this case.

    If the PQ team would like to produce a #shared function that returns a list of user defined objects that exist in the target model (be it Excel / PBI / SSAS (fingers crossed) / SSRS (fingers crossed)) then that would be super :)

    Thanks,
    Simon

    Monday, October 24, 2016 4:12 PM

All replies

  • Create a new DM table with your list of queries. Then, change your M routine into a function and populate argument with list number.

    Unrecognized Excel MVP (UEM)

    Monday, October 17, 2016 5:30 PM
  • Hi Simon,

    In place of the direct references to the tables (e.g. fctData, dimData), try using the following pattern:

    Record.Field(#shared, "fctData")

    Assuming a dynamic tableName value is passed in, you could do this:

    Record.Field(#shared, tableName)

    Ehren

    Monday, October 17, 2016 7:39 PM
    Owner
  • Assuming a dynamic tableName value is passed in, you could do this:

    Record.Field(#shared, tableName)

    Ehren

    Thanks Ehren!  This is the close but not quite what I'm looking for.  

    When I say dynamic, I really mean dynamic.  So at no point should a user ever enter in "fctData" - or any query name, for that matter.

    The goal of this query is to execute it or against a PBI model or Excel workbook and return a consolidated list of Table.Profiles().

    With this in mind - ideally I'm looking for a way to filter out all of the Power Query's built-in functionality from #shared so it returns a list of user defined queries only.  I would also need to filter out the current query too or I'd get a circular reference.


    Thanks,
    Simon


    • Edited by Simon N Tuesday, October 18, 2016 1:41 AM
    Tuesday, October 18, 2016 1:38 AM
  • Just some rough thoughts here (assuming you're not working with 2016 where you could probably use VBA?):

    - transform the current version of default-#shared into a constant list and use List.Difference to return only the queries of your current workbook

    - alternatively connect to an external workbook where default-#shared-result will sit in a worksheet - you then just have to update this workbook with every new PQ-update to keep this up to date

    - place your dynamic query on top in the editor: As #shared seems to respect the sort order, you could then skip the 1st element of that list

    hope this makes sense and would work :-)


    Imke Feldmann TheBIccountant.com

    Tuesday, October 18, 2016 6:30 AM
    Moderator
  • Another idea: Start all your queries with a common prefix (or set of prefixes). Then filter the #shared list for only those prefixes.

    Example (which assumes all your queries start with "fct" or "dim"):

    let
        Source = Record.ToTable(#shared),
        #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "fct") or Text.StartsWith([Name], "dim")),
        #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Table.Profile([Value]))
    in
        #"Added Custom"

    Ehren

    Tuesday, October 18, 2016 7:24 PM
    Owner
  • Thanks guys

    At the end of the day I don't think there's a way to do it without referencing the default #shared list. This would work of course, but it would require maintenance with every PQ update and therefore defeat the purpose of creating the audit script.

    Great idea with the naming convention idea Ehren.  However, my goal with this script is to audit other peoples models so unfortunately that won't work in this case.

    If the PQ team would like to produce a #shared function that returns a list of user defined objects that exist in the target model (be it Excel / PBI / SSAS (fingers crossed) / SSRS (fingers crossed)) then that would be super :)

    Thanks,
    Simon

    Monday, October 24, 2016 4:12 PM