Data preparation has become so easy and potent in Excel and Power BI. The user experience of the Query Editor of Power BI and Excel (Power Query Add-In, or Get & Transform in Excel 2016) is extremely rewarding. It changes…
Last month the Power BI team at Microsoft released an enhanced “combine binaries” experience, that I covered here last month. That new functionality allows you to easily combine multiple CSV files (and other file types) from a folder and utilize…
I have been working fairly extensively with Finance teams, leveraging Power BI to build financial Models, dashboards and reports. (See some of my recent posts Financial Dashboards, Personalized Dashboards and Q&A). Regardless of the team we’re working with, a very…
Usability Can your data model (Power Pivot or Power BI) easily be used by 100s of users? Is your model fairly intuitive to understand for the end-user even with little or no training? Usability may be the key to achieving…
"Do we use Power Pivot or Power Query?" – I get this question all the time when teaching Power BI classes. (Note: This applies in Excel as well as Power BI Desktop world. Even though in PBI Desktop the tools…
Guest Post by Andrew Todd
Intro by Avi: Andrew has been a part of PowerPivotPro family for a while now and has been doing wonders for the clients he has been working with. You have probably heard Rob talk about the “Data Gene”, well Andrew is the ideal specimen for that breed.
Recently I had worked with a client where we spent quite some time just trying to figure out how to connect Power Pivot to their Oracle database. Andrew here lays it all out in simple step by step approach. Andrew’s got a lot of wisdom to share and you should all look forward to more posts from him. Take it away Andrew…
The amount of data around us is staggering. Statistics citing the amount of data being generated daily describe it in terms of exabytes (1 exabyte is 1 BILLION gigabytes!) In fact, the amount of data generated daily now exceeds the entire storage capacity of the entire world in 1986!
In a world with that much data, we’re lucky to have tools like Power Pivot and Power Query that offer easy connection to data sources that just sort of … clicks! However, if you’ve tried connecting to an Oracle database in Power Pivot you might have had some trouble. Fear not, here’s how you can make your Oracle database connections click just like all the rest!
Why aren’t my Oracle connections ‘clicking’?
For Excel pros, Oracle databases are a bit different, because there are a few key connection enabling components that are not automatically included with most users’ setups:
1.) Connection details – for Oracle databases, the connection details are included either in the connection string itself (EZConnect) or in a flat file called TNSnames.ora. Don’t worry, it isn’t as complicated as it sounds!
2.) Oracle data providers – You can think of these as the communication link between Power Pivot and the Oracle database.
Make a Call to your DBA for some details!
Before you attempt to connect to your Oracle database, you’ll need to contact your friendly Oracle DBA for the following information:
The more I use Power Query, the more I want to use Power Query – and of course the more I learn. I have seen quite a few references on the Web to “consolidating multiple Excel Workbooks into a single table”, but I came across a problem working with a client recently where I had to undo someone else’s ‘helpful’ partitioning of data into separate worksheets in the one workbook. You may have come across the problem yourself. Someone has been collecting data over time, and they start a new sheet every month.
Well intentioned but actually quite unhelpful.
The procedure I use here to bring this all back together in a single table is the same basic concept as consolidating multiple workbooks, however I have learnt a thing or two since the first time I copied someone else’s pattern on how to do this. Specifically I have learnt the correct and easiest way to create and work with Power Query functions.
The basic process/pattern is:
- Create a query to import a single sheet of your data. Do all the transformations you need here.
- Convert the basic query from step 1 above into a function (the easy way) while keeping some backwards capability.
- Create a second query that uses the function from step 2 and consolidates all the objects (Sheets in this example), but the same principles apply for any other object.
There are other approaches you can use to do this (as is often the case with Excel) but this procedure demonstrates how easy it is to write Functions if you understand the basic concepts.
First Create a new blank workbook edit a single sheet
Go to Power Query Menu\From File\From Excel. You will see in the navigator the list of sheets. Just select any one of these sheets in the list. For this process to work, the column structure of the sheets needs to be the same.
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…
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.
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: Tim Rodman
There is finally an ERP product that gets it, that embraces Power Pivot, Power BI, and the more than 800 million users of Microsoft Excel.
“What Power Pivot did to Excel, Acumatica is doing to the world of ERP”.
This is huge. It’s as if two worlds are colliding, creating a good kind of explosion.
You: “So, wait a second, what is ERP?”
Me: “Great question, I should probably back up for a minute.”
What is ERP
ERP stands for Enterprise Resource Planning and it is the computer system that ties all of the departments in your organization together.
If you work with Power Pivot, there is a very good chance that you also work with ERP data.
SAP, Oracle, Microsoft Dynamics, Infor, Epicor, Sage, and NetSuite are all examples of ERP systems.
Many of the ERP systems in companies today are very old and very ugly. The problem is that they are expensive to replace so they continue to exist through a patchwork of duct tape fixes that have been cobbled together over the years.
However, ERP systems contain a virtual Fort Knox of data that can lead to incredible insights if analyzed correctly (with Power Pivot of course).
The ERP Problem