We’ve gone through what Power BI Desktop is, with Power BI Service is, what Power BI moBIle and Power BI for developers is and you’re probably wondering how do all those pieces fit together.
This is exactly what we are going to do in this video. We’re going to go through a typical workflow on how to analyze data and we will see how each component fits into that process, how each component helps you get the solution that you need.
At the end, I will do a live demo of Power BI so you can actually see it and feel it and say: “oh this is how it looks like”. For that, we will do a quick report and we will go through each step so you can see how everything fits together.
First of all, the workflow.
How do we analyze data?
Think about it because you’ve done this when you work with Excel, but maybe you haven’t reflect on it. The process is exactly the same except for the end.
The first thing you need to do is to get data. You have to get data into Power BI the same way you would get data into Excel.
To get data into Power BI, we’ve talked about this, you need Power Query.
You have two types of sources where you can get data from: you can get data from your local sources that means Exce,l text files, CSV that will be also SQL on-premise, you know everything that you have at your disposal locally and then you have the possibility to get data from online sources and Power BI both the Desktop and Service offers you a few connectors to do that.
You have for example on the Service a Google Analytics connector, you have MailChimp connector, you have Azure connectors both on the Service and the Desktop. There are lots of ways to get data online there is a really Powerful connector called web connector that allows you to get data from websites. It is really good and Microsoft is being perfecting this and it works really nice. You have to test that!
If you need a connector that is not available, then you can actually create your own. there are a lot of developers that has been creating connectors for all kinds of things, for example: you have connectors for WooCommerce and you have connectors for running applications. There are connectors everywhere! you know on github…. just google it but if you still can’t find your connector you can code your own and you will do that using Power Query and the M language. If you don’t know how to do it you need to get help from consulting firms, but it’s absolutely possible to do.
Now that we’ve got the data inside Power BI, the next thing we need to do is what we always do, we need to clean it.
Think about it, you do this in Excel very often,, if not always! Every time you get data in, you have to remove columns, add columns, remove products, products that you have to change names.
To do all that data transformation you’ve got a very Powerful tool in Power BI to help you do that and it is Power Query. Power Query, by the way, is available also in Excel. Make sure you use it, is absolutely fantastic!
You have two ways to clean data: you can use the user interface you know the point-and-click where you have a lot of functions in there that do most of the jobs needed, but if you know a little bit of M, M is the language that Power Query uses to clean data, you can just code it.
M is a language that was created for Excel users so you don’t need to have any coding experience at all, you can actually learn M without any problem and it is very very intuitive. I’ve found it very intuitive! Of course, it can get very complex but for your daily data clean needs is this absolutely fantastic!
Model the data
Now we’ve clean our data, the next thing we need to do is to model it.
We talked about it also in a previous video, you can create a data model in Power BI. It sounds a little bit as a fancy word but what you basically are doing, in Excel you normally would create a big table right? You join tables, your join columns and you do vlookups just to make a big table and then you can start analyzing data.
In Power BI you don’t need to do that, you can leave your tables as they are and you create relationships between the tables. If you work with Access a little bit you will have very easy to do this.
If you are not very experienced in doing these things, there are two things you can do: number one if your IT team has already created a model for you, for example SQL database, when you import our data, the model will get imported for you:the relationships we get created automatically and you don’t need to worry about that. The second way is: there is an option on Power BI which is turned on by default and it is to detect relationships. Power BI will try to detect relationships between tables by itself and and it does a pretty good job at it, but you know sometimes it doesn’t get it right so if you see the things are getting filtered a little bit weird, probably your relationships are not correctly set. That is another tutorial for another day!
We have our data model in place and now we need to ask questions.
The data model and asking questions, you do it in PowerPivot and again you have it as an add-in in Excel.
Dax is a language that was created also for Excel users and it uses the same syntax, so the functions sound the same: you have SUM, you have COUNT, do you have COUNTROWS in Excel? I think you do, but you have the normal functions that you will find in Excel, which is a good thing because it gives you an idea of what it is what that function does, but it doesn’t work the same way and I think that is a pity because you might think: “oh I know this function, I know what it does” but then you will find that when you start using it it doesn’t do what you think it should do and complicates things a little bit.
So yes, Dax is not an intuitive language to learn, you have to really put time to learn it but it is absolutely worth it! The upside of this is, that you can create powerful reports without writing almost no DAX or any DAX at all actually and I will show that on the example I’m going to show you at the end of this presentation.
Visualize your data
Now that we have our data model and we have all the measures, all the formulas, that we need, we need to visualize our data.
Visualize it and then share it.
To visualize it, we have a Power view in Excel which is available in Power BI too.
You have quite many visualizations inside Power BI out-of-the-box: you have line charts, bar charts, column charts map charts, matrix, you know tables, you have all kinds of visualizations but if for any reason the visualization that you want is not there, you can go to the office marketplace and try to find the visualization there.
There are developers that are doing visualizations for Power BI and most of them are free, but there are some premium visualizations also. If you still don’t find your visualization you can still create your own, we talked about it on Power BI for developers.
Share the data, report
So, you used the visualizations, you created your report and now it’s time to share it to the world, right? So, how do you do that?
If you remember you publish it to the Service, to the cloud and then you can share it with both publicly or internally, your colleagues, your friends, your partners, the web, whoever you want no problem!
You have the possibility to embed the report somewhere else so you don’t have to use Power BI dot-com you can just take your report and just put it in your app or on your platform or on your website or whatever you need or you can visualize your report on the Mobile Apps, we talked about that: so you have for iPhone and for tablets.
So, now you can start sharing your hard work with others and start gaining insights from your data. Now that you know the workflow, how about we just do a practical case, we do a demo of how all those things works step by step.
Let’s do that now!