If this is your first exposure to Power Pivot and Power Query, you're probably a bit overwhelmed by all the features and options available. That's normal. No one is going to become a Power BI expert in one day. The journey toward proficiency in DAX and the M Query Language takes time.
The good news is that you don't have to take this journey alone. Plenty of resources out there can help you on your path. Here, you'll discover a few resources you can leverage when you need a push in the right direction.
Let Excel help write your formulas
When starting with Power Pivot's DAX formulas, one of the best places to get help is the Insert Function dialog box. Next to the formula bar in the Power Pivot window, you'll see the button labeled fx. This button opens the Insert Function dialog box. Similar to the function wizard found in standard Excel, the Insert Function dialog box allows you to browse, search for, and insert available DAX functions. This comes in handy when you need a reminder on how to use a particular DAX function.
Use the Help system
Don't forget that Microsoft has an extensive Help system tied to both Power Pivot and Power Query. The Help system is often the fastest and easiest way to locate extra help on a topic. When you search for help, Excel checks to see whether you're connected to the Internet. If you are, Excel returns Help results based on online content from Microsoft's website. The content you find with the Help system is up to date and often contains links to other resources not available offline.
"Pilfer" examples from the Internet
If you're stuck trying to perform a particular task, fire up your favorite online search engine and simply describe the task you're trying to accomplish. For the best results, enter the term Excel Power Query or Excel Power Pivotbefore the description.
For example, if you're trying to write a macro that deletes all blank rows in a Power Query model, search for Excel Power Query delete blank rows. You can bet two months' salary that someone else on the Internet has tackled the same problem. Nine times out of ten, you'll find the nugget of information you need to jump-start some ideas.
Leverage user forums
If you find that you're in a bind, you can post your question in a forum to get customized guidance based on your scenario.
A user forum is an online community that revolves around a particular topic. In these forums, you can post questions and have experts offer advice on how to solve particular problems. The folks answering the questions are typically volunteers who have a passion for helping the community solve real-world challenges.
Many forums are dedicated to all things Excel. To find an Excel forum, enter the words Excel forum in your favorite online search engine.
Here are a few tips for getting the most out of user forums:
- Always read and follow the forum rules before you get started. These rules often include advice on posting questions and community etiquette guidelines.
- Use concise and accurate subject titles for your question. Don't create forum questions with abstract titles such as "Need advice" or "Please help."
- Keep the scope of your question as narrow as possible. Don't ask questions such as "How do I build a Power Pivot dashboard in Excel?"
- Be patient. Remember that the person answering your question is a volunteer who typically has a day job. Give the community time to answer your question.
- Check back often. After posting a question, you may receive requests for more details about the scenario. Do everyone a favor and return to your post to either review the answers or respond to follow-up questions.
- Thank the expert who answers your question. If you receive an answer that helps you, take a moment to post a thank-you to the expert who helped you out.
Visit expert blogs
A few dedicated Excel gurus share their knowledge by way of blogs. These blogs are often treasure troves of tips and tricks, offering nuggets that can help build up your skills. Best of all, they're free!
Although these blogs don't necessarily speak to your particular needs, they do offer articles that advance your knowledge of Excel and can even provide general guidance on how to apply Power Pivot and Power Query in practical business situations.
Here's a starter list of a few of the best Excel blogs now on the Internet:PowerPivotPro, the Data Pig Technologies blog, Excelguru, and Chris Webb's BI Blog.
Mine YouTube for video training
Some of us learn better when we watch a task being done. If you find that you absorb video training better than online articles, consider mining YouTube. Dozens of channels are run by amazing folks who have a passion for sharing knowledge. You'll be surprised at how many free, high-quality video tutorials you'll find.
Go to YouTube and search for Excel Power Query or Excel Power Pivot.
Learn from PowerBI.com
PowerBI.com is Microsoft's site dedicated to helping you get a quick start in developing solutions with Power Pivot and Power Query. The site offers lots of free examples and documentation. Although it can be a bit difficult to navigate, it's worth a visit to see all the free resources, including sample workbooks, tools, step-by-step instructions, and much more.
Continue your journey with other books
In addition to Excel Power Query & Power Pivot For Dummies (Wiley), other books on the market cover the details of the Power BI suite found in Excel. If you're interested in continuing to expand your skills with Power Pivot and Power Query, consider picking up books from other authors to see more advanced perspectives on how to use these tools.
For Power Pivot, a good supplemental book is The Definitive Guide to DAX,by Alberto Ferrari and Marco Russo (Microsoft Press). Ferrari and Russo provide an excellent overview of the more advanced techniques for extending Power Pivot with DAX.
For Power Query, a good next step is the book M is for (DATA) MONKEY, by Ken Puls and Miguel Escobar (Holy Macro! Books). It includes a plethora of advanced examples and scenarios that expand on Power Query's M formula language.
Dissect the other Excel files in your organization
Like finding gold in your backyard, the existing files in your organization often comprise a treasure trove for learning. Consider cracking open those Excel files and have a look under the covers. See how others in your organization use Power Query or Power Pivot and try to spot new techniques. You may even stumble upon DAX recipes or chunks of useful M query code that you can copy and use in your own workbooks.
Ask your local Excel genius
Do you have an Excel genius in your company, department, organization, or community? Make friends with that person today. Most Excel experts love sharing their knowledge. Don't be afraid to approach your local Excel guru to ask questions or seek out advice on how to tackle certain problems with Power Pivot or Power Query.