Post by Rob Collie I’ve known Ken Puls and Miguel Escobar for many years now. They’ve been “in” on this Power BI / Power Pivot revolution basically from the beginning. In fact, Ken pressuring me was the reason I wrote…
Microsoft Excel Power Query for self-service BI professionals.
Post by Rob Collie
From Last Week’s Client Work
Last week a client asked us to solve a somewhat unusual problem: given any two lists of Twitter followers, tell us how many followers “overlap” between the two lists.
How Many of Han Solo’s Followers Also Follow Leia Organa, and Vice Versa?
(Randomly-generated Twitter handles are funny. I particularly like “@Gommo” and “@Xxfok”)
Loading the Data: Using Power Query
Let’s use Power Query to perform the import this time, both because we’re using PQ a lot more around here now that we have Power Update, and because we’re gonna need PQ for the more complex steps later.
Note that all of the steps below are performed using Excel 2013. (I find Power Query to be a bit too clumsy in Excel 2010.)
Importing from a Table Using Power Query: Step 1
(Unchecked “has headers” because of the “Han Solo’s Followers” Row)
by Matt Allington I guess most of the readers of PowerPivotPro.com will already have a pretty good idea that Power Query is awesome. I spent time recently thinking about how Power Query has really opened up the data on the…
Guest Post by Gil Raviv
Intro: Many experts had proclaimed LongForm Journalism was headed towards extinction in the digital age. In fact it has found a new resurgence and a new audience in the recent years. Thanks to that we can still get articles like This Old Man (featured on NPR’s best Longform Journalism list). This blog post is in the same spirit. This is not a bite-sized learn a cool new trick. We do have tons of those on our site and they’re great. But sometimes you want to sit-down and eat a seven-course meal. Enjoy the feast! Take it away Gil…
In this blog post we will show you how to use Power Query in Excel to import data from LinkedIn and gain amazing competitive analysis insights based on company search. To get your attention right at the start, we will conduct this tutorial and analyze a domain that we all know so well and love – Power Pivot. We will analyze companies who specialize in Power Pivot.
We will show you step by step how to utilize Power Query to extract information from LinkedIn including company size, founding year, location, specialties, and more.
Build your own customized Competitive Analysis Dashboard
When we are done, you can download the workbook, read below how to get LinkedIn access token in this tutorial (Step 1-6), and start using the workbook as your dashboard for competitive analysis. You can use its parameterized queries to search for companies in any domain, refresh the workbook and get a tailor-made dashboard for the competitive posture of your interest.
Before we start, here are few screenshots of what you can get at the end.
Next screenshot shows the distribution of companies by founding year. It’s interesting to see a gradual incline of younger companies who specialize in PowerPivot from 2009 to 2013, and to see a decline in 2014. It seems that last year fewer companies were founded with PowerPivot as a specialty.
I am sure that by now, we got your attention 🙂
Guest Post by Willem van Dijk
Intro by Avi: I have never met Willem van Dijk in person, but in my mind he has a persona of a scrappy boxer/standup comedian. A priceless combination. He would have you rolling on the floor no matter what. He brings home the reality of a business user trying to get some business intelligence. Your day job is something else – Finance, Marketing, Accounts, Sales, Product planning etc. But you do what it takes to get the data and analysis that you need. Here’s to the fighting spirit! Take it away Willem…
I love to hate secrets just about as much as I hate to love secrets…
Why? Because knowing a secret is great for the ego, yet too much ego is not great for the soul.
Which is why I love reading posts on PowerPivotPro as it is all about sharing secrets which, in essence, are no longer such.
Ironically, at times what one believes to be a secret often results to be common-knowledge, or worse, common-sense.
In my inaugural post I promised that I would share a Pot-roast recipe (of the secret kind) so here we go…
by Matt Allington In my last blog on PowerPivotPro.com I showed how easy it is to create a standard calendar for Power Pivot using Power Query. Most of my customers however don’t use a standard calendar but instead use a…
by Matt Allington In this post, I am going to show you how simple it is to create a custom calendar using Power Query. If you follow the steps below, you will get a good sense of how capable Power…
***Update #1: a Free Version of Power Update is now available. More info here.
***Update #2: There is now a forum for Power Update questions, located here.
Intro from Rob: I’m what you might call a “gift horse optimist” – strongly positive outlook, but when the hoped-for thing finally arrives, I find myself closely inspecting it, testing it, before I trust it enough to advocate it to others. I went through this same process with Power Pivot itself – I “saw” its gamechanging power in 2010, but it was a full eighteen months before I finally dropped all disclaimers and just started calling it far better – period – than anything we’ve had before.”
Similarly, I’ve long known that Power Update would be a MAJOR win for us in the Power Pivot and Power BI communities. But I am willing to advocate it now only because I’ve watched others – like Scott, and Tim below – use it successfully, in production environments, in recent months. (Also see my post last week “introducing” Power Update in case you missed it).
Take it away, Tim…
I first found out about Power Update two months ago via a LinkedIn post by Christian Floyd.
It took me a while to realize that he wasn’t talking about a theoretical future idea, but an actual product, something that exists today. Click the picture below to see the entirety of my foolishness. It wasn’t until I talked to him directly that I realized what Power Update really was and I was immediately interested.
He got me a beta version of Power Update and I began testing it at the company I work for: a manufacturing company in Cleveland, OH called The Robbins Company.
We started using Power Pivot at The Robbins Company back in 2013 and I wrote about our experience on this blog (click here).
Post by Rob Collie
***Update: check out Scott Senkeresty’s review of Power Update over on Tiny Lizard.
***Update #2: a Free Version of Power Update is now available. More info here.
***Update #3: There is now a forum for Power Update questions, located here.
A brand-new software utility designed from the ground up as
a “Companion” to Power Pivot, Power Query, and the entire Power BI stack.
Definitely Click on the Image for Larger Version – Surprises Lurk Therein
Do Any of These Sound Familiar?
Power Update Helps With ALL of These (And a Few More, Too)
“What IS It?”
OK, a few things:
Power Query Magic: The Ultimate and easiest way to consolidate multiple tables, sheets, text and/or csv files
(Click for Full-Size Version)
At some point in the life of an Excel user, we have all faced a similar dillemma. How can I combine multiple sheets, tables, csv or txt files? (can I combine them all together??)
How we used to solve this scenario
Back in the day (before Power Query) we actually had some ways to do so but they were not so user-friendly and they relied heavily on coding or some tedious way of doing it. The most common ways were:
- Using SQL Statements to join multiple files
- Creating a VBA code that will do the job for me
- Going with the tedious way of combining the files manually (perhaps with Excel or Access)