Switch from Excel & PowerPoint to next-Gen dataviz (for Beginners)

[augustindelaf]

The purpose of the following article is to help people who would like to create simple dashboards for their Excel data, and to show them how easy it is.

Let's start with a very common issue we all faced once.  You have (a lot of) data in your Excel file, and until now you always built your reports with Excel charts and PowerPoint. But you have seen some Power BI reports, and you would love to be able to create some beautiful reports too.  

Find the Excel data set and the Power BI file (.PBIX) attached to this Blog Post.

 

How can you do that? Very simple!

We are going to explore some common Excel models together, to help you migrate your data hoping that our way is close to yours. Our first model is a finance/management controlling one. Sure, many of your spreadsheets look a bit like that.

In this model: Units by Month, each column is a new Month, each row is a new KPI (Sales, Gross Margin, Net Profit).

Important: Just make sure the Months cells are interpreted by Excel as Dates, and not as strings. To check that, click on one of them and look at how it is displays in the Formula bar. It should be like that (If you need to change it, click right on the cell and go to 'Cell format', and Select 'Date'):

Logically, your Excel chart should look like the following: 

To create a similar chart in Power BI, It's a matter of seconds. Let's open Power BI Desktop. We first click on 'Get Data'>'Excel', and find our file.  Sorry for the screenshots that are in French.

Let's move to the query editor (Power Query). Power Query sees the file as it is in Excel. Same data, same structure.

The first row contains labels for all months and for KPIs. We need to keep that, and to use it. We are going to use a popular function: Keep first Row as Headers (see below). 

To put all this data in a chart, we need to perform a very important step: Unpivot Data. That means all columns with dates (January>December) need to be merged. Technically, our 12 columns will be transformed into 2 columns: 'Attribute', 'Value'. A standard chart only contains 3 or 4 fields in Power BI. (We currently have 12 date fields!) We need to unpivot data if we want to enjoy the capabilities of Power BI charts.

Let's do it. Select the First column (January) and hold MAJ until the last column (December). Then, click right and select the option "Unpivot columns".

We now have 2 columns instead of 12, Attribute and Value. We can rename the field "Attribute" to "Month", and change its data type to 'Date'. For the field 'Value', data type must be changed as well, to a "Decimal value" type (because involved data are integers or decimals). 

You are finished with the Query Editor.  Click "Close and Apply" on the top left, you will leave the Query Editor and go back to Power BI Desktop.

You can now see 3 fields on the right: KPI, Month, Value. 

In the Visualizations Pane, select a Curve Chart and drag the appropriate fields: 'Month' for the Axis, 'KPI' for the Legend, 'Value' for the Values. You now have a nice chart!

Add a slicer visualization and drag the 'Month' field. Add another slicer and drag the "KPI" field. This is now a basic Dashboard.

As you can see, transforming data from Excel to a Power BI chart is quite an easy thing. More articles soon! Thank you for reading this post.

My First Power BI Dashboard.pbix