How do I get my data into Power BI?
So you’ve decided you’re going to use Microsoft Power BI for your data analytics reporting. A great choice! You’ll gain detailed, visual reports and it’s an easy, no-code solution. But perhaps you’ve already discovered - hence why you’ve landed on this page - that getting your data into Power BI, isn’t always as simple as using the tool itself.
OK, before I go on, just to be clear, I’m not talking about how you get your data into Power BI, once you have it in a clean, neat and organised spreadsheet - from there it’s plain sailing (there’s also heaps of Microsoft articles explaining what you need to do step-by-step.)
I’m talking about how the heck you get this clean, neat and organised spreadsheet in the first place. This article looks at the whole process from start to finish, because I know, if I googled ‘How do I get my data into Power BI’, I’d want to know the whole shebang.
Unsurprisingly, you have options. I’ll now cover the main options, with a little bit of advice on how complicated, time-consuming and costly each option is. By the time you’re done reading (so in about four minute’s time) you should have a pretty clear idea on your favourite approach.
Power BI integrations.
Firstly, it’s worth knowing that some third party data sources (data sources being the software you’re using to get your data out of and into Power BI) can be automatically integrated with Power BI. There’s a built-in connection. Google Analytics, Salesforce and unsurprisingly, Dynamics 365 and other areas of the Power Platform are the main integrations. Here’s the full list.
It’s not a bad list, but let’s be frank, it is heavily skewed towards Microsoft products, as you might expect. However, if these basic integrations cover your needs, then you may feel that all you need to do is follow a few steps in Power BI (or the third party software), for some you download an app, and hey presto! You’re done! But unfortunately, it’s not quite that simple - never is hey!
These in-built integrations come with an honest warning that, whilst Power BI will attempt to keep this API integration continually updated, they make no guarantee. This could be a little risky. It basically means that if there are any changes to Power BI, or the third party software, Power BI can’t guarantee the data transfer will continue to work in the same way. Eek!
To be honest, this isn’t really a worry for integrations from Microsoft’s own software like Dynamic 365 and the Power Platform - you’d expect them to be pretty on top of their own products. But if there’s a change to a third party software, like Salesforce, you could start to see some discrepancies in your data.
You’ll also need to consider ‘rate limits’. Rate limits basically mean there are limits on how much data you can transfer at once. These integrations have rate limits and don’t always flag when you’ve reached your limit. This could really mess with your reporting as it would mean you’re not seeing all your data and you wouldn’t know. Eek again!
So these built-in integrations are free and simple but they do come with risk. Also, at some point you may want integrations outside of this list and you’re likely to be out of luck if you use niche, industry-specific software platforms as these aren’t included the list in the first place.
API integration
If you want to manually get your data out of your data source and into Power BI then this can be done via an API Integration (Application Programming Interface). As the fancy name suggests, this isn’t the simplest option. If you’re not a developer yourself, you’re very likely to need developer support and it’s worth knowing that the documentation for this API is pretty huge.
When you (or a developer) has got to grips with the API, you have to consider that this API has ‘rate limits’ - yes, that again. And it’s worth also noting (again) that if you do hit these rate limits, not all your data will be transferred. And it’s not always immediately obvious that that’s happened. Eek once again!
Once you’ve got your head around these aspects, you will also need to store your data somewhere - a data warehouse. Our advice is to choose this warehouse very carefully. You’ll pay more for the better warehouses but it’s going to be a home to all your data, potentially data from across multiple systems, so it’d be pretty disastrous if there was a security breach.
Finally, when you have this set up, you need to create a ‘data model.’ This is basically the template for how you make sense of your data. So your data model is the grouping and organisation of your data - what does each field show, how does it relate to other fields and more. Sounds pretty complicated and to be honest, it can be. And it’ll get more complicated, the more data sources and data fields you have.
But this is an absolutely crucial step, as it will define what your data tells you. If mistakes are going to happen, this is generally where they happen. And a seemingly small mistake can really impact what you see.
Moving onto the positives with this option, once all this is set-up, it should be easy to transfer data from your source and into Power BI in future too. You may need to make some tweaks, but you won’t need to go through this process every time. And of course, it’s all done in-house, which is important to some companies who like complete control.
Many people see this as the ‘free’ option, so it’s worth debunking this myth a little. Of course, you’ll need either in-house, agency or freelance developer support but there will also be a cost associated with the data warehouse - if you don’t have your own of course.
So we’ll tell you straight up, this is the most complicated option. But you may not have a choice but to do this manually, if Microsoft doesn’t have an in-built integration to your source, or you can’t find an ETL tool that supports your software.
…Did you just ask what on earth an ETL Tool is? Keep reading.
ETL Tool
This option is arguably the most popular. An ETL tool (Extract, Transform, Load, another fancy name) will basically follow the process outlined above, but all the API integrations and data modelling has already been developed. In other words, all the hard parts are done. So, as a user, you do nothing more than log-in to Power BI and log-in to your software, then go, go, go. (Although, with some ETL tools it might not be quite that simple, so do check the process for each ETL tool before you start using it.)
With this option you will still need to store your data in a data warehouse but a good ETL tool will take care of this for you. But the same advice applies - check the credentials of the data warehouse being used. If the ETL tool’s website, doesn’t tell you about the data security side of things, alarm bells should ring.
This option isn’t free (but as outlined above, there is a misconception that doing it yourself is free.) Usually with an ETL tool you’ll pay per month rather than upfront, and these costs should include data storage but always check. And if you can, use a tool without a contract, so if you change business systems and your new system isn’t one of the integrations your ETL tool supports, you’re not lumbered with paying for a tool you can’t use.
Like with the API integration, once your integration is set up, it’s done for future too, you don’t have to log into multiple systems each time. And with some ETL tools you can set automatic updates, so you know the data is being refreshed every week, day or few minutes - It’s up to you. This is actually a pretty great feature, you’ll never forget to refresh your data before a meeting or presentation.
—-
So there you have it, the full picture on how to get your data out of your data source(s) and into Power BI.
Which option you choose will no doubt depend on factors such as how much time and resources you have to dedicate to this. Which system(s) you’re using and whether or not you can find and ETL tool that supports them. And I guess your company’s policies around tech and tools.
No matter which option you plump for, we wish you luck - you got this!
