Creating a Power Query function browser – #powerbi

One of the best functions in Power Query (M) is for sure the function =#shared that inside the Query editor lists all the functions in M and in your current pbix file.

Picture 1 Result of =#shared

But sometimes you do want to check out the documentation online and then you have to leave Power BI desktop – so I thought would it be possible to use Power Query to scrape the documentation (link) – and create a Power BI Report in which I could browse the documentation.

And because the documentation is well structured is turned out to be quite easy

Let’s start by getting all the function categories

The start page of the Power Query M function reference contains a nice list of all the categories

So begin with we activate a new query with data from the web

And by using the new feature “Add tables using examples” we get a lot of help

Just type in the first 3-4 elements in the “In this section” list and we get a nice result

Click on the edit button and we have a nice list of the categories in our Query editor

Now each of the categories points to a specific web page

And the address for all of them is the name and the hyphens instead of spaces so I do a simple replace values where we add a new column with the relative path

And to get the function category URL I add a parameter called PQ Documentation URL that points to the relative path and that I can use in my other queries as well later.

And then add a column where I concatenate the path and the name of each categories relative path

And now we have a reference to all the pages of the Power Query function categories.

Let’s create a function to extract all the functions in a category

Now to create a table of all the functions I create a new query and reference the first Query I made

And the idea is then to create a custom function that returns a table of all the functions within a category

Each page of contains tables of the functions

And when creating functions I prefer to create a query that works and then turn it into a function so let’s dig in – and the navigator sometimes tries to help us a little bit to much so I rightclick the address and choose edit.

And we get a nice structure of the html page.

We only want the tables and as you can see in the preview each table is actually a list of the functions

Expand the Custom1 column table

And voila – a nice list of all the List functions in this case is returned

The other columns can now be removed, and we are ready to turn this into a function

Open the advanced editor and modify the query from

To

And we can use this function to add as a column in our function query to get all the functions in the different categories

So now we have all the categories and the functions within

Now to the hard part – get the description of each function into the model

Each of the function has a specific page – in this example the List.Accumulate

The address of each of these function pages is the name of the function https://docs.microsoft.com/en-us/powerquery-m/list-accumulate

With a hyphen instead of the . (dot) – so constructing the address is quite easy.

But I only wanted to extract the data within the black square.

By looking at different pages I found out that all of the information I wanted was located in a specific named element <pre>

So on each page I have to locate that tag and then the get all the html for there to a named element called </main> that marks the end of the area.

Now when we use the Web query interface we get a lot of help but in this case we do not want the help

So after clicking Edit in the navigator menu

We get this – and this is because the Web.Contents is wrapped in the Web.Page function

So by modifying it to Text.FromBinary instead we get the html in text returned instead

Now we can click the Fx to create a new step to find the <pre> tag

And another step where we still refer to Source and find the position of </main>

Now as the final step – we extract the html from the source by using the Custom1 and Custom2 value with some offset

And we have the HTML from the page that we wanted.

And we can turn it into a function

Finally I reference the previous query and create the query with all the syntax and example text

And we are ready to create the data model

Now click close and apply and create a datamodel with the following relationships

I created a measure to the create a default URL to the documentation if a category isn’t selected

And sat the data category to the different URL’s as Web’s url

And the rest is slicers and presentations

To present the HTML extracted from the function page I use the custom visual “HTML Viewer”

And the final report looks like this

And now I can hit refresh and have an updated Power Query Documentation report

Example file and comments

I hope you find this useful and if so please like or comment if you have ideas or feedback.

Link to the pbix file is – here

Or if you just want to browse the report – https://app.powerbi.com/view?r=eyJrIjoiMDczMGE5NjMtMjA4Zi00NmE4LTlmODAtZDRhMWQzMTk3ZmUzIiwidCI6ImUwN2Y3YzBiLTk5NWItNDg0NS1iZWJhLTQxNmYwZDYxZTFlMSIsImMiOjh9