Tag Archives: data analysis

What is Microsoft Power Query?

Business Intelligence, excel, excel training, Microsoft Power BI, , , , , ,

Power Query is a data connection tool available in excel and Power BI that enables users to discover, connect to data, transform the shape of data as per user requirements and then load it into an analysis tool for further use, usually into excel or Power BI.

Power query is an “ETL” tool. ETL stands for Extract, Transform and Load.

Extract – get data from data sources into power query.

Transform – reshape the data into usable format, e.g. by removing columns or rows, combining columns, manipulate text, pivot or unpivot data and so on.

Load – load the reshaped and now user friendly data for further use.

Whilst several ETL tools have been available in the market, they are mostly used by technical IT professionals. The entry of Power Query opened a new chapter for ordinary business users who could now access an ETL tool built specifically for them.

Where do you find Power Query?

Power Query comes fully integrated into Power BI Desktop where it is called “Get Data”. It is found on the Home Tab’s Get Data Group.  In Power BI Desktop, data can only be loaded using Power Query/Get Data.

In Excel, Power Query started off as a free addin (excel 2010 and 2013). After you install the Addin, a new POWER QUERY Tab is added on the Excel Ribbon. 

From Excel 2016 onwards, Power Query was fully integrated into Excel and renamed “Get and Transform”.  To find it you navigate to the Data tab and then you will see the group named Get and Transform.

Want to learn more about Power Query? Check out this upcoming Power BI course. Power Query will be in action, Extracting, Transforming and Loading data.

What is Microsoft Power Pivot

Business Intelligence, excel, excel training, , , , ,

Power Pivot is a free add-in for Excel that enables you to perform data analysis and create data models that are more sophisticated than what you can build in Excel.

What can Power Pivot do?

  1. Work with huge Data Sets

The maximum number of rows available in Excel is 1,048,576.

With Power Pivot for Excel, there is theoretically no limit on the number of rows of data you can have. Any limitation will depend on the version of Microsoft Excel installed on your computer and whether you are going to publish your spreadsheet to SharePoint or not.
On 64bit version of Excel, Power Pivot can handle about 2 GB of data, but you need to have enough RAM to make this work smoothly. Power Pivot for Excel can handle millions of records (rows). If you reach the maximum number of records, excel will give you an error.

2. Combine Data From Different Sources

Excel has always been able to handle different data sources, such as SQL server, XML, Microsoft Access and even web-based data. The drawback comes when you need to create relationships between various data sources. Whilst you can use third-party products or excel functions such as VLOOKUP, INDEX MATCH, to “join” data, they are too cumbersome and impractical for large datasets. Power Pivot for Excel was built to handle such scenarios. Within Power Pivot, you can import data from virtually any data source.

3. Create Visually Appealing Analytical Models

Power Pivot for Excel lets you output a variety of visual data to your Excel worksheet. You can return data in a PivotTable, PivotChart, Chart and Table (horizontal and vertical), Two Charts (horizontal and vertical), Four Charts, and a Flattened PivotTable.
The power comes when you create a worksheet that includes several outputs, which provides a dashboard view of the data thereby making analysis much simpler and more interesting. Users will be able to interact with your spreadsheet if you build it correctly. You can make use of slicers and buttons to create interactive report filters.
4. Use DAX to Create Calculated Fields for Dissecting Data

DAX (Data Analysis Expressions) is the native language used in Power Pivot tables, primarily in creating calculated columns that help you to further enhance your data analysis capabilities.

Why not give it a shot?

Verifying data in excel – example

Business Intelligence, Payroll, , ,

Joyleen verifies data

assemble challenge combine creativity
Photo by Pixabay on Pexels.comClick

Click the link below to download the accompanying workbook.

Data verification example

Madota Limited has entered into an agreement with Bank Izzy Bank to provide payroll banking services for the company and all of its workforce. After accounts for all 300 employees have been opened, Joyleen receives a printed document with the list of all employee bank account numbers. In order to capture the information in Madota’s HR system, Joyleen needs to convert the data into a soft copy. She therefore assigns an HR intern to reproduce the list in excel. After the list has been typed, Joyleen has to verify if the account numbers are complete. Upon browsing the document, Joyleen notices that some of the account numbers have fewer or more digits than the standard length. She therefore needs to quickly identify all account numbers that to do not meet the specified length without having to go through each account number. Joyleen was advised by the bank that all account numbers consist of 10 digits.

How is Joyleen going to achieve this?

Analysing excel data – Joyleen’s Predicament, can you solve it? – Solved

Business Intelligence, Payroll, ,

assemble challenge combine creativity
Photo by Pixabay on Pexels.com

Joyleen’s Predicament

Please download the accompanying workbook to solve the problem. Workbook link is provided below.

Download the workbook here

Download the solution at the end of this article.

Simba and Joyleen work for Madota Limited in Finance and HR departments respectively. Joyleen has been asked to carry out an earnings analysis for the past 3 years for each employee and has managed to extract data presented in the above report (see spreadsheet provided), TABLE A, from the HR system with all 300 company employees (here we have provided just an extract)

Joyleen then realises that the HR system can only present the data for the year, department and employee in one column only but the report required must have those in individual columns. Using her excel skills, she would have been able to use the text to columns function in excel to separate the 3 columns but she requires a dynamic solution that uses formulas because there is a likelihood that the same report will now be run on a regular basis. She just needs a template on which she will paste the source data and the formulas do the work for her without repeating the text to columns process.

Joyleen decides to get help from Simba in Finance, Simba is known in the organisation for his fairly advanced excel skills.

The question; If you were Simba, how would you solve the above and provide a permanent solution for Joyleen? Please write your formulas in TABLE B – SOLUTION”

For further practice, you can attempt to fill in TABLE C, separating the surnames and first names. Enjoy!

For those interested, the solution to this problem will be shared on this page via a download link to the solution workbook at a later date, stay tuned.

Download the solution here