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
nice information keep sharing.