Reusing the Power BI and Excel files with New Data
In the previous parts of this workshop, we have prepared a report and a dashboard in Excel from the Lead Merchants year 2019 sales results. We also made reports on Power Pivot and Power BI Desktop.
Now the sales results for 2020 so far has arrived, and we want to do the same reports with 2020 results. As we will go on the same processes again, we can use some automation; we can reuse the files we prepared already, but this time with 2020 figures.
The sales database for 01/01/2020 to 20/08/2020 has 1.472.008 rows. In this part, we will first run Power BI reports we prepared in the 4th part of the workshop, then we will use the flat-file of Power BI and run the Sales report in Excel we have prepared in the 2nd part.
1- Lead Merchants 2020 Sales Analysis in Power BI
Open the LM Analysis of 2019 Sales.pbix file, and rename it as LM Analysis of 2020 Sales.pbix. Now we will replace the 2019 database tables with 2020 tables.
Under the Home menu, select Transform Data>Data source settings.
In the Data source settings dialogue, we have to change the path for the files from 2019 to 2020, for each table.
Select tables one at a time, click the Change Source button at the bottom, in the new dialogue click Browse and find the table on your computer. Do this for all tables.
The Data source settings dialogue now shows the path for all new tables.
Press close and PBI applies the changes and replaces 2019 tables with 2020 ones.
That’s it! You updated everything; tables, reports, the Dashboard for 2020 all show 2020 results. Go to the sales report and examine the new figures.
Have a close look at the first table. We only have 8 months now, and we have only 20 days of August. When we examine the figures until the end of July it is clear that the sales were increased in 2020. There is a fluctuation, the sales reached a peak in April – May and then decreased.
It is possible to load 2020 figures in the same files as 2019, but this will be a new job—this time we want to prepare our standard reports and present them as fast as possible. We will do that in the connecting to systems and databases part, connecting with a system.
Our Power BI reports are ready, and we will go on with the Excel reporting.
In the next part, we will run the 2020 reports on Excel.