2- Lead Merchants Analysis of 2020 Sales on Excel
The Flat File table has 182.835 rows.
The Flat File was also updated with 2020 figures together with everything else. We will make an adjustment in the flat file, which was something we have overlooked last time.
The file is good for use in our Excel report, but the column order is different. So there will be manual work when we move out of PBI. To make this easy, we will rename the columns of the flat file, put an alphabetic character at the beginning of each column name, and later we will do a sort in Excel. It is possible to sort the columns in PBI or create a new table using the Flat File table’s columns in the right order and make this part automated also for later use, but we will keep this simple and do the sorting in Excel.
Below is the column order we have used in Excel, and we will rename Power BI table columns to match this order.
| Excel Report Columns | Match | Add | Power BI Table Columns |
| month | a | a | Month |
| customer_id | b | f | ProductsTable.Product |
| customer_name | c | c | CustomersTable.Customer Name |
| product_category | d | g | CountryTable.Country |
| product_id | e | l | EmployeesTable.Employee Name |
| product | f | e | Product ID |
| country | g | b | Customer ID |
| country_id | h | d | ProductsTable.Product Category |
| region | i | o | ProductsTable.Cost (£) |
| region_id | j | n | ProductsTable.Price |
| employee_id | k | h | CustomersTable.Country ID |
| employee_name | l | i | CountryTable.Region |
| total_sales | m | j | CountryTable.Region ID |
| price | n | k | CountryTable.Employee_ID |
| _cost | o | m | Units Sold |
The flat file in the previous part was also updated with total revenue and cost columns.
Now rename all 15 columns of the PBI flat file by adding the letter in the Add column. Below is an image of this in Power Query, where we do most of the data and table operations, but renaming columns can be done directly in PBI Desktop also.
While in the data view, right-click on any cell or column of the table and select Copy table. Wait until PBI stores the table into memory and then open an empty Excel workbook and select cell A1 and paste the table.
Next, open the Sort dialogue, under options, select Sort left to right, press OK.
Select Row1 in Sort by and press OK.
The file is sorted and ready to use for the report.
Now open the LM-Analysis-of-2019-Sales.xlsx file we prepared before and save it as LM-Analysis-of-2020-Sales.xlsx.
Go to LM_SQL_to_Excel_Flat_File sheet in the report workbook, select cell B1, Ctrl+Shift+End, press delete and clear all the data. You must keep the column names unchanged so that PivotTables could work properly.
It takes a while for Excel to respond after deleting data because all the formulas are recalculated with every change in data.
Now copy and paste the 2020 flat file we have prepared, to this sheet. It will again take a while, and all formulas will be recalculated. Now save the file.
Go through the sheets of the workbook.
In the report sheet, the last 3 months show 0 results.
In the Quarterly sheet, in third-quarter results, September shows 0.
The Pivot sheet still shows the 2019 results. The Pivot Tables do not update automatically.
In the PivotTable Analyze menu, select Refresh, Refresh All.
A warning message, the CustomerCharts pivot table in the hidden sheet Dashboard Tables overlaps with another table.
It is not always a good idea to work with that many pivot tables and set them close to each other. Pivot tables resize with the data, and this creates some problems. The CustomersChart was set to show the top 3 customers by profit, and right under it, there was the employees table. It appears that the second-biggest profit amount, 1056, was achieved by a multitude of customers, and the table wants to show them all, overlapping the employees table.
Unhide the Dashboard Tables sheet, move the CustomerChart pivot table, select cells B5:C19, cut and paste cell E21.
Now Refresh All, and all pivot tables in the workbook, the dashboard tables and visuals are updated.
Hide the Dashboard Tables sheet, save the workbook, reporting is ready.
We changed the Tables in Power BI, and the Power BI reports and the flat-file were ready. This would normally take 10 minutes. We spent another 10 – 15 minutes renaming the flat-file columns, and this will not be done again in the next run.
We sorted the flat-file columns and replaced the data in the Excel workbook, which also took around 10 minutes, and the reports on the workbook were ready. Then we wanted to update the pivot tables and the Dashboard. We had to deal with an error that took another 10 – 15 minutes. This will not happen in the next run.
We have prepared both the Excel reports and Dashboard and the Power BI file in less than an hour. Each took hours of work when we first prepared them, going forward they will be finalised in minutes. This is the main point of automation; design to reuse and recycle.
Next, we will look at another feature in Power BI, a direct connection to systems and databases, which eliminates loading the tables and is one step forward to real-time reporting.