Navigating the sea of data One of the many things which amazes me about Power Pivot time after time is its ability to effortlessly convert raw data into crucial information as soon as it happens. For example, receivables not being paid…
Many accountants consider that cash flow statements are the most important indicator of a company’s performance, as they do not involve estimates or judgments of the preparers. While indirect method cash flow statements can be prepared easily from P&L…
From the Water Cooler As a 'water cooler' of sorts for this community, we meet some amazing people. Matt Mowrey shares with us a really useful technique to create a discoverable income statement using PowerPivot. This post runs a little…
Welcome to the latest PowerPivotPro Coffee Talk, where members of the community discuss various topics related to Power BI, Power Pivot, and Analytics/BI in general. These conversations take place during the week on a Slack channel, and are then lightly…
Intro by Avi: We know that Power Pivot and Power Query are extremely useful tools in the Finance and Accounting world. Today we have a guest post by Darren Leitzke outlining a very useful scenario and sharing some good tips…
I Bet You Think This is a Picture, Don’t You? Yeah, this is the place in most posts where you will find a picture… but aha! It is anything BUT! That’s not a picture, it’s a fully interactive Power BI…
Power BI Desktop View of Our Clients by State (Map) and Breakdown by How We Help Them (TreeMap) (Yes, this is Quickbooks Data in Power BI, and it was Painless. See Below for Examples Using Power Pivot Too) Too Legit…
Post by Rob Collie
First, a Few Quick Updates…
1) Just a reminder that enrollments are open for the live, in-person two-day classes taught by me (Rob) in Indianapolis and Washington DC (in October and November, respectively.) Sign up now – those dates will be upon us sooner than we all realize!
2) The pre-order for DAX 2nd Edition is going Great! Thanks to everyone who has pre-ordered and/or contributed. PRE ORDER HERE if you have not already, and don’t forget the exclusive swag rewards like the sticker, poster, and t-shirt.
3) Ola Freaking Rosling is now using Power BI, and threw us a “shout-out!” OMG, check out this tweet. I nearly peed myself:
If I hadn’t heard so much Anti-Microsoft-Propaganda, I would’ve solved my data-integration faster with Power BI & Power Query @powerpivotpro
— Ola Rosling (@OlaRosling) September 16, 2015
If you don’t recognize the name, his dad is Hans Rosling, the speaker in the first TED talk I ever watched:
Click Image to View the Talk
…In which he did the animated bubble chart demo that I’m 100% positive was the inspiration for Amir Netz adding animated bubble charts in the next two product releases
Here’s Ola and Hans co-presenting on the state of world health and social justice, via data of course:
Again, Click to View the Talk
A celebrity in that Google-y, Apple-y, Silicon Valley-y “doing good with data in the public interest” space using Power BI and saying that Power BI is awesome, and that he’d previously been scared off by all the anti-MS propaganda… well that is simply awesome.
But I won’t lie – that he gave US a mention in that tweet – I *do* find that to be even cooler. I’m human. I can’t help it. I smile every time I think about it.
Slaying the “White Whale” of Variable-Rate Forecasting with PRODUCTX
“Arrrrr! We Be Meeting Again!”
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)
Guest Post by: Mike Griffin
Intro by Avi: Power Pivot and Power BI tools can be used to transform BI for a wide array of industry verticals and vocations. But it is especially suitable to some roles; Accountants are probably at the top of that list! Our friend Mike here is a Financial Manager in the interesting vertical of Cruise Lines. And has a post for us describing just one of the ways they are using the Power BI tools, in this case to find needles in the haystack. Take it away Mike…
Accountants are NOT typically data GEEKS
Accounting related problems open doors to a different set of applications for PowerPivot and PowerQuery. Although it’s fair to say most accountants like numbers, an affinity for numbers does not always translate into a love for data – especially lots of data. This example illustrates how PowerPivot and PowerQuery can be used to help automate accounting related tasks that can be incredibly time consuming when a lot of data is involved.
The scenario I’m presenting is not sophisticated in terms of DAX formulas and is very simple from a data modeling point of view. However, it’s an incredibly useful application of the tools we use as PowerPivot enthusiasts that can save valuable time when closing the accounting period.
The Accounting Need: Remove needles from the hay stack
Use Power Pivot and Power BI to look for the proverbial needles in your data haystack
In this scenario, I need to reverse invoice specific journal entries that were originally posted as part of an automated process between an internal database and our accounting software. This entry is posted as a batch with thousands of other invoices (the original journal entry can’t just be reversed).