Power BI is an Analytics and Business Intelligence platform. Users work with data tables, establish relationships, improve them with formulas, prepare reports, aggregate and present the data with visualisations. Power BI Service is an extension that allows working on the cloud and presenting data online through websites and mobile platforms.
Excel is a spreadsheet software with robust features that evolved through decades for financial reporting and modelling. Power Pivot brought mass data analysis capabilities to Excel supported with its presentation features.
Professionals working with data and doing these operations in a recurring manner develop a sense of automation; they want the systems and software to do more of the work for them. All the tools we worked with in this workshop supports a level of automation; you do not do the same things you have done in the first run.
Both Excel and Power BI have features enabling automation for improving the workflow on analytics and business intelligence. The process of building reports, dashboards and visuals already has a level of automation; all the actions from loading the tables, adjusting them for analysis, the reports and visuals are set up one time and reused with new data.
In this part of the workshop, we will run 3 processes:
- We will reuse the Power BI file we prepared for 2019, but this time we will use the year 2020 data. We will replace the data tables of the 2019 database with 2020 tables.
- We will also prepare the Excel reports with the flat file we get from Power BI 2020 file.
- We will make a direct connection to PostgreSQL. This part is to simulate connecting Power BI to a system/database to automate the analysis and presentation.
In this part we will analyse 2020 results with Power BI, then run the 2020 reports on Excel and finally connect Power BI with PostgreSQL to analyse 2019 and 2020 results and have a look at the AI features.
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.
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.
3- Connecting Power BI to Systems / Databases
In this part, we will simulate a connection to a live database that business data is input and edited continuously as in a usual business system. We will connect the PostgreSQL (PSQL) database, edit and input data in PSQL, examine the changes in Power BI reports.
If you followed the workshop since the beginning, you must have already installed PostgreSQL and done some database operations in it. It is a good idea to revisit this part briefly before starting today.
If you did not follow the first part and don’t want to use SQL now, you can still follow this part of the workshop. We are not necessarily working on SQL; we are using PSQL as if we have connected to a live system.
As usual, you can download the database tables. There is also a disconnected Power BI file which shows the end state of our work in this part.
Connect to database
Open PBI desktop application, open a new file and go on with getting data.
There is a big list of file types, databases, services, platforms etc. that you can connect and get data in PBI; PSQL is one of them.
Select the PostgreSQL database and click Connect. In the new dialogue write the P-SQL server host and database names. You can see the host name in PSQL, on the left pane under the servers, right-click on the server name where the LM database resides. In our case the server name is Sales, from the dropdown menu select Properties and select Connection from the dialogue; now you see the Host name/address.
You can see the database name in the left pane under the server. Both are case sensitive:
Server Host name : Localhost
Database: Lead_Merchants
Now input them in the PBI dialogue, and select DirectQuery. This means we will stay connected to the database and get updates as soon as we press the refresh button. On the contrary, the Import method will get the data in PBI.
With the direct connection, we establish automation, where we can get updated data as frequent as we want with a click of a button. And because we do not import the huge tables into PBI, we also end up with very small file size. After we finish the workshop, we will import all the tables in order to share the PBI file of the workshop.
Press OK, the Navigator dialogue opens, where you will see all the tables in the database. The tables that are necessary for analysis must be added to PBI; we will select all tables by checking all of them.
Press Load and tables will be connected to PBI.
Examine the tables in Model view, check relationships between tables.
Recognise that there are only Report and Model view buttons on the left side of the PBI window. The Data view button is not displayed as all the tables are a link to a database, and they are read-only. We can still extend the tables in PBI by adding columns, measures or creating new tables from current ones.
We will go on and prepare the Power BI file we have prepared in the fourth part. Go on and follow the steps in Power BI: Data Analysis for Business Intelligence add measures.
We will now go on and manipulate data in our system (PostgreSQL), and examine the output in PBI.
Create a new page, name it as Sales Dashboard put the filled map, resize it to fill the report, put a region filter at the bottom of the page. We will change the country data and examine it in this table.
Now select Europe in the region filter. As you can see Russia and a few other countries that are geographically in Asia are grouped in Europe, in Lead Merchants sales grouping.
Management decides to move Russia and other countries to Asia sales group, so an operator changes the region of Russia. The operator would probably work on a screen where the country definitions are made and adjusted, but we will directly change the database in PSQL.
Enter the code
SELECT * FROM countries WHERE region = 'Europe'
Press F5
We see the list of European countries, including Russia.
We will update the countries table and change the region of Russia to Asia PBSQL.
UPDATE countries SET region = 'Asia' WHERE country = 'Russia'
Go to PBI, click Refresh on the Home menu, and examine the change in Europe.
A more familiar Europe map.
In 2020 some new clients, from South America, opened online accounts with Lead Merchants.
We have to update both countries and customers tables.
These would be defined in designated screens which eventually update the database. We can do these additions by simply loading new data on existing tables or by writing code.
To see both, we will update the countries table with code and then append the new customers list to the customers table in PostgreSQL.
The below code is adding 12 countries in the countries table.
INSERT INTO countries(country_code, country, region, region_id, employee_id)
VALUES ('AR', 'Argentina', 'South America', 'SOAM01', 'MV03'),
('BO', 'Bolivia', 'South America', 'SOAM01', 'MV03'),
('BR', 'Brazil', 'South America', 'SOAM01', 'MV03'),
('CL', 'Chile', 'South America', 'SOAM01', 'MV03'),
('CO', 'Colombia', 'South America', 'SOAM01', 'MV03'),
('EC', 'Ecuador', 'South America', 'SOAM01', 'MV03'),
('PY', 'Paraguay', 'South America', 'SOAM01', 'MV03'),
('PE', 'Peru', 'South America', 'SOAM01', 'MV03'),
('UY', 'Uruguay', 'South America', 'SOAM01', 'MV03'),
('VE', 'Venezuela', 'South America', 'SOAM01', 'MV03'),
('SR', 'Suriname', 'South America', 'SOAM01', 'MV03'),
('GY', 'Guyana', 'South America', 'SOAM01', 'MV03')
RETURNING *
Together with 12 countries, we also added a new region, South America, to our table.
Now refresh the PBI and select South America, the new region, in the region filter. South America is now filled as a region.
Now it is time to define the colour shading in Format Painter to make our choropleth map.
As we do not have any transactions from South American customers in 2019, the region has a whiter shade of blue.
Now we will update the customers table and load new tables from LM 2020 database.
Right-click on the customers table on the left pane of PSQL, import the Customers20. Follow the import procedure explained in the first part.
Create Datetable and Orders20 tables.
Create orders20 table as defined in the previous part.
Now import the datetable and orders20 tables.
Datetable is related to orders (2019) and orders20 tables, and it is necessary to compare columns from separate years.
Set relationships for the new tables.
ALTER TABLE public.orders20 ADD CONSTRAINT "customers_FK" FOREIGN KEY(customer_id) REFERENCES public.customers(id);
ALTER TABLE public.orders20 ADD CONSTRAINT "products_FK" FOREIGN KEY(product_id) REFERENCES public.products(id);
ALTER TABLE public.orders20 ADD CONSTRAINT "date_FK" FOREIGN KEY(order_date) REFERENCES public.datetable(id);
ALTER TABLE public.orders ADD CONSTRAINT "date_FK" FOREIGN KEY(order_date) REFERENCES public.datetable(id);
Refresh the PBI and see the new tables and examine relationships in the model view.
Examining the sales results for 2019 and 2020
We will create a new table for comparison of 2 years results.
The orders_conso table will be created by measures.
First, rename the orders table as orders19. Now public.orders19 and public.orders20 tables, correctly naming the years they involve.
Select any table, from the Table tools menu select New table.
Rename the new table as orders_conso.
Add the below measures to the orders_conso table.
Revenue_19 = SUMX('public orders19', [units_sold]
* RELATED('public products'[price]))
Cost_19 = SUMX('public orders19', [units_sold]
* RELATED('public products'[p_cost]))
Units_19 = SUM('public orders19'[units_sold])
Profit_19 = [Revenue_19] - [Cost_19]
Revenue_20 = SUMX('public orders20', [units_sold]
* RELATED('public products'[price]))
Cost_20 = SUMX('public orders20', [units_sold]
* RELATED('public products'[p_cost]))
Units_20 = SUM('public orders20'[units_sold])
Profit_20 = [Revenue_20] - [Cost_20]
Finally, we must add a date hierarchy to datetable, as PBI did not create the date hierarchy on the tables with the direct connection.
The id column in datetable shows the dates. Click on the three dots on the id column and select New hierarchy.
The id Hierarchy is created under datetable. Now click dots on the year column, select Add hierarchy, id Hierarchy. Do this for month and m_name, in this order. The date hierarchy is ready.
Go to the sales report page, Select Table visual, add the month and m_name fields from date hierarchy, Units_19 and Units20 fields from orders_conso table.
As you can see we have full year’s figures for 2019 and only 8 months until 20 August for 2020. For comparison purposes, we will examine the first-half results.
Add a slicer, click the little arrow on the top right, choose Between and set the slicer to 1 to 6 months.
Now we can compare the first-half results of LM sales.
As seen from the table, there is a significant increase in sales in the first half of 2020. There is also a fluctuation; sales increase sharply until April then start decreasing in May. We will add an Area chart visual to examine this fluctuation.
Add regions by profit table, products by units sold table and revenue by month table to Sales Report.
Add a Clustered column chart to compare the top 3 countries by profit.
Argentina is among the top 3 countries contributing the profit in 2020; where there were no sales in this country in 2019.
Add a multi-row card for units sold, revenue and profit totals of both years.
The visuals you can use in Power BI are much more than you see in the visuals pane. There are other visuals developed by Microsoft and third parties, you can import and use in your reports.
Under the Infographics category add Scroller. The scroller visual is added under VISUALIZATIONS.
Add scroller, select employee name and revenue_20 fields, set filter to Top 5 by revenue.
Add a Slicer for regions and set the type to the dropdown.
The Sales Report is completed.
Rename the Sales Dashboard page as Sales Map 2019. Right-click on the page tab and duplicate this page.
Rename the new page as Sales Map 2020. Change Revenue field to Revenue 20 field. You have to redo the Format Painter Data setting for the year 2020.
Now we have a choropleth map of 2020.
AI
There are 3 AI visuals in Power BI: the Q&A, the Decomposition Tree and the Key Influencers. These come very handy when you work with data that you are not very comfortable with, especially when you newly start the analysis. But after examining data in various tools in this six-part workshop the AI visuals will not look too impressive.
We will use 2 of these visuals very basically.
Q&A
This is a visual where you can ask some questions on the data, get fast answers, sometimes a number or a graph, and then convert this answer to any other visual.
We create a new page in the report and name it Q&A. Put 2 Q&A visuals on this page and resize them to have a full view. As you can see there are already some questions suggested by the visual. We will ask our own questions.
First question: What is the profit by month in the year 2020?
While we are asking the question the tool suggests words to better understand the question. For example profit for the year 2020 is suggested as profit_20 as we have defined it in the measure. It returns the months in a chart from highest to lowest.
The second question: What are the units sold by product category? Again wording improved by suggested words. You can simply select a question suggested by the visual also.
Now you can change these Q&A visuals with any other visual, such as a table or pie chart, so you will have built these visuals without playing with measures and fields.
Decomposition Tree
With this visual, you can examine your data with various breakdowns.
We created another page for this visual: Add a decomposition tree and resize it to fill the page.
Select Revenue20 from orders20 table. A bar appears in the visual representing the revenue. We will have a breakdown of the contributors to this revenue.
Then select the product category. When you select the first field to breakdown, a small + sign appears in the revenue bar.
Click on the plus sign and a small dialogue asks High value or Low value. Select high value and first breakdown appears, revenue by product category.
Select region and then click plus sign then select employee name.
Now the revenue is broken down into very high granularity. We see the revenue by product categories that sum up to the total revenue, then each regions contribution to the revenue of the selected product category, and employees performance for each region.
You can select different product categories or regions on the tree to analyse the granularity, or restart the breakdown and start with country or product etc.
This is a very useful tool for analysis if you get hold of it, in fact, you may want to start your analysis with this tool and learn about the dimensions of the data you want to examine.
The Key influencers is used to analyse the drivers that influence the key metrics. For example, we could analyse the units sold by price and see the effect of price on sales. It seems that this tool is not working well on LM sales data.
There are other AI functionalities embedded in PBI other than these three visuals. But these visuals are useful for end users like us.
We have finalised the analysis. As a final step, we will import the database tables so that this can be a shareable file.
Save the file with a different name. Open Query editor, go to any table, under the applied steps select source. PBI now shows Switch all tables to import mode. Click this button, Close & apply changes and save the file.
In windows explorer, you can see that the size of the imported file is significantly big, 67.798Kb.
Publishing to Power BI Service with a direct connection requires the set-up of an on-premise gateway, which we will not do at this workshop.
Also, when the connection to Power BI Service is set up, real-time streaming can only be done with certain dataset types.
We can still publish the imported file to Power BI Service and go on with publishing and distribution of reports.
This is the end of the six-part workshop on data analysis tools. I hope you have enjoyed working with these tools as much as I have. Please write your comments or ask any questions in the comments section at the bottom of this page or by email.
Stay well.
Go to Automation for Analysis and Reporting.
Go to the Data Analysis workshop.