cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

M Function to return a list of all queries residing in Power Query?

Is there such a thing?  I've looked through the msft library with no luck (also via "=#shared" in PQ)

 

I need a dynamic and automated way to generate this list to pass off as a parameter to another function.

Let me know if you have any suggestions.

 

Thanks!

 

Igor

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: M Function to return a list of all queries residing in Power Query?

Check =#sections[Section1]
it is a record, you can get its field names then.
Beware of recursion!
Maxim Zelensky
excel-inside.pro

View solution in original post

7 REPLIES 7
Highlighted

Re: M Function to return a list of all queries residing in Power Query?

Check =#sections[Section1]
it is a record, you can get its field names then.
Beware of recursion!
Maxim Zelensky
excel-inside.pro

View solution in original post

Highlighted
Helper III
Helper III

Re: M Function to return a list of all queries residing in Power Query?

Oustanding Maxim; pretty sure this will do!

I am wanting to create a query that will automatically scan through (any) model tables/queries, ID "date" / "datetime" fields, determine MIN and MAX values and then feed those as parameter inputs into a query function that generates a dimCalendar table (FNdimCalendar) automatically.  Step one is to ID the tables which can then be passed off to something like Table.Schema function etc.  You just paved the initial portion of the path.

 

Side questions:

 

1) your comment to "Beware of recursion" - can you please elaborate?

 

2) Are you aware of any command etc. that would allow the query grouping structure to be accessed as well?  Below excerpts are those of a poor example (essentially testing model) - I am referring to "Sources" and "Other Queries".

Sections 01.png

 

 

Thank you again for the input!

 

Igor

Highlighted

Re: M Function to return a list of all queries residing in Power Query?

HI Igor
Nice idea, I think. I previously made function to create such calendar from one table, but not from all
Sometimes when I used this #sections trick, it didn't work inside the queries... do not sure what is the reason.
Concerning your questions:
1) you should filter out your query Pay_App_Data, for example. Sections shows you all queries and functions and parameters etc., including this query itself. You should find a way to select only tables there (you can filter it by type, but it is a little bit tricky)
2) don't sure there is a way to reach it by M. I think it is only UI, and not implemented in M
Maxim Zelensky
excel-inside.pro
Highlighted
Helper III
Helper III

Re: M Function to return a list of all queries residing in Power Query?

1) That is precisely what I had in mind.  I am not seeing why it should be tricky but let me dig into it and I am sure I will find something else to quiz you on 🙂

2) 10-4.  As part of my research, I reached out to some Microsoft engineers via email.  Should I actually get a reply, I will be sure to ask this questions as well and will let you know what they say.

 

Happy holidays!

Highlighted
Anonymous
Not applicable

Re: M Function to return a list of all queries residing in Power Query?

Awesome question, and I think I have a solution to only keeping the tables.  Essentially, a try statement that attempts to count the number of columns in a table (all tables will have at least 0).  -1 is thrown in case of an error.  Then just keep the rows with a value >-1

 

 

 

let
    Source = #sections,
    Section1 = Source[Section1],
    #"Converted to Table" = Record.ToTable(Section1),
    #"Add TableTest" = Table.AddColumn(#"Converted to Table", "TableTest", each try Table.ColumnCount([Value]) otherwise -1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Add TableTest", each [TableTest] > -1)
in
    #"Filtered Rows"
Highlighted

Re: M Function to return a list of all queries residing in Power Query?

HI Chris
It could be done a little bit easier after converting Section1 to table:

Table.SelectRows(Table, each [Value] is table)

IS - operator for type comparison
Maxim Zelensky
excel-inside.pro
Highlighted
Anonymous
Not applicable

Re: M Function to return a list of all queries residing in Power Query?

Never knew about "is".  That's an awesome solution!

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors