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/
What i do not realize is if truth be told how you’re not actually much more neatly-appreciated than you may be now. You are so intelligent. You already know thus significantly in relation to this topic, made me in my opinion imagine it from a lot of various angles. Its like women and men don’t seem to be fascinated except it¦s something to accomplish with Girl gaga! Your own stuffs nice. All the time care for it up!
Great V I should definitely pronounce, impressed with your web site. I had no trouble navigating through all tabs and related information ended up being truly simple to do to access. I recently found what I hoped for before you know it in the least. Reasonably unusual. Is likely to appreciate it for those who add forums or something, web site theme . a tones way for your client to communicate. Excellent task..
Just desire to say your article is as surprising. The clearness in your put up is simply excellent and that i can think you’re an expert in this subject. Fine with your permission allow me to grasp your feed to keep updated with imminent post. Thank you one million and please carry on the rewarding work.
I do agree with all the ideas you’ve presented in your post. They’re very convincing and will certainly work. Still, the posts are too short for newbies. Could you please extend them a little from next time? Thanks for the post.
Fantastic items from you, man. I have remember your stuff prior to and you are simply too great. I really like what you have obtained here, certainly like what you are saying and the best way by which you are saying it. You’re making it enjoyable and you still take care of to stay it smart. I cant wait to read much more from you. That is really a great site.
I have learn some excellent stuff here. Certainly price bookmarking for revisiting. I wonder how much effort you put to make any such magnificent informative website.
When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get several e-mails with the same comment. Is there any way you can remove people from that service? Thank you!
fabuloso este conteúdo. Gostei bastante. Aproveitem e vejam este site. informações, novidades e muito mais. Não deixem de acessar para saber mais. Obrigado a todos e até a próxima. 🙂
demais este conteúdo. Gostei muito. Aproveitem e vejam este site. informações, novidades e muito mais. Não deixem de acessar para saber mais. Obrigado a todos e até a próxima. 🙂
Excellent website. Lots of helpful info here. I’m sending it to several buddies ans additionally sharing in delicious. And of course, thanks for your sweat!
Hello.This article was really remarkable, especially since I was investigating for thoughts on this subject last Tuesday.
Very well written story. It will be beneficial to everyone who utilizes it, including yours truly :). Keep up the good work – i will definitely read more posts.
Hi, Neat post. There is a problem along with your web site in internet explorer, may test this… IE nonetheless is the marketplace chief and a huge component to other folks will omit your excellent writing because of this problem.
I like what you guys are up too. Such clever work and reporting! Keep up the excellent works guys I have incorporated you guys to my blogroll. I think it will improve the value of my web site 🙂
Nice post. I study one thing more difficult on different blogs everyday. It’ll at all times be stimulating to read content material from other writers and apply a bit something from their store. I’d prefer to use some with the content on my blog whether you don’t mind. Natually I’ll provide you with a link on your web blog. Thanks for sharing.
Having read this I thought it was very informative. I appreciate you taking the time and effort to put this article together. I once again find myself spending way to much time both reading and commenting. But so what, it was still worth it!
Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?
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.