I wrote a blog post on PowerPivotPro.com last week on how to create a standard Power Pivot calendar using Power Query – from scratch. I was doing some research this morning on how I could extend this concept to create a 4/4/5 Calendar (which is often used in the Retail Industry). I was looking at this on-line reference documentation to find a function for a particular problem, and it was really hard to find what I was looking for. There is no index at the top of the page, just a very long page of sections and formulae.
Then I had a brilliant thought – I wonder if I could use Power Query to download the Power Query documentation? I tried it out, and the answer is a resounding yes. This is what I did.
I copied the link to the web page. This is the one I was using
I then opened a blank Excel workbook and went to the Power Query tab and inserted the above link in the box.
The Power Query Navigator returned a long list of formula categories, so I knew I was onto something.
I then right clicked on the folder at the top of the list (not the tables), and selected edit. Then in the window that opened I expanded the tables.
BAM – exactly what I was after.
I did a bit of tidying up, loaded the data into my workbook, added a slicer and I ended up with a useful Power Query reference tool that is auto updated with the latest additions from Microsoft.
My guess is it is no accident that the data is laid out in a way that works with Power Query. Anyway, I have attached the finished workbook here if you would like to use it. Download the Power Query Workbook
Edit: 2 March 2015. There is 1 problem I discovered with this tool, but the good news is there is is also a simple fix. The problem is that when you are editing a Power Query workbook, Excel is in a “Modal” state. That is to say, that you can’t be in Power Query editing a workbook and then also Alt-Tab to a different Excel window (eg this Power Query documentation window) and use the slicer. So that means you can either filter the documentation using the slicer or you can be in edit mode for the Power Query Workbook you are working on – but not both – not ideal given the exact time you need the documentation most is when you are editing in Power Query. But there is a solution. The trick is when you need to open the documentation, you should open a second instance of Excel. In Windows 8, one easy way to do this is to click the Windows Start Button, type “RUN” and press enter, then type “excel /x” and press OK in the dialog. If you have a macro workbook you will get a prompt saying the personal.xlsb is already open, but you can just take any of the options and it will work just fine.
If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/power-query-online-training/
At least in Win 7, you can open another instance of Excel by holding down the ALT key, open Excel, and release the ALT key when a dialog box asks if you want to open another instance. Here’s a nice link: http://blogs.office.com/2013/06/03/opening-workbooks-by-running-separate-instances-of-excel/
Looks awesome! I am not a fan of the functions by category. I would rather have an alphabetical list.
When I try to connect the web site, I get an error “cannot be retrieved using Web.Page as it does not appear to be a web page.” Any ideas? I’ve tried changing the URL and using both IE and Chrome as my browser with no change in result.
I just downloaded the file myself and tried it – it works for me. I would suggest opening the Power Query workbook and going to the first step (source) and re-entering the URL. Browse to the URL yourself first (try the link I have provided above first) to make sure the page loads natively.
Wow! Thanks, I’d just printed out the Power Query formula reference docs again for the nth time.this will make it much easier to find the right formula to use.