I was chatting with my friend and colleague Meagan Longoria one day. She is a passionate BI consultant/extraordinaire who works with me at BlueGranite. We both enjoy good beers and began chatting about a microbrewery database I created some time ago and use frequently in presentations. It came up that I had created the dataset the hard way with a lot of Excel gymnastics and SQL Server. At the time, Power Query (or it may have still been called Data Explorer) sounded like it had potential to do exactly what I needed it to do. However, when I tried to use it against the website it would pull in my data but it would break it out like this:
You can see it breaks each record out into multiple tables. I knew that problem could be overcome by merging them. The problem that vexed me though, was how to overcome the issue that it had one record broken out on two rows of each table.
Meagan who does quite a bit with Power Query, took my issue as a challenge. She details how she solved the issue with use of Pivot and some customization to the M script in her blog. See how she was able to resolve my brewery data woes here.
Is your organization deploying Microsoft Power BI?
BlueGranite offers hands-on, instructor-led training at your facility to help both business and IT teams adopt Microsoft Power BI for self-service business intelligence and analytics. Up to ten attendees will receive expert-led guidance through a complete set of hands-on labs and training modules. After the training, attendees will be able to acquire data, build data models, and create visualizations quickly and easily with Microsoft Power BI. Click here to learn more.
