Power Query for Data Analysis
Published: 2023-02-16 19:22:59

When starting to learn Data Science or Analysis mostly of the people takes Excel to be a minor option as they thought it can't make things done.

In reality Excel is the most used Spreadsheet program, easy to use and it's completely free. Apart from Data Analysis it can be used to clean, store and manage data.

It has business intelligence tools like Power Query, Power Pivot and Power Maps for data analysis and development of business insights.

In this article I will write on how to use Power Query to save time and perform analysis of the data.

## 
What is Power Query?
Power Query is the most powerful tool for data analysis and transformation in Excel. It's also Known as Get and Transform in the Microsoft Excel 2016.

## Where to find it.
Those using Microsoft Excel 2016 it's available at the ribbon as Get and Transform. 

Also for those using Microsoft Excel 2010 and 2013 it can be downloaded at [Microsoft](https://www.microsoft.com/en-us/download/details.aspx?id=39379).

All you need to do is check the version of your Microsoft Excel through Account button on Excel.

![Excel Account](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t0iy92raomgynq6f4jdf.PNG)
Then check about Excel and the information of the Microsoft Excel will be seen on top whether it's of 2010 or 2013.

![About Excel](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b2zz3s1cucu7bwxzbv8q.PNG)  
![Description of the Excel Version](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/atrqbq4phjen3jlxhf0q.PNG)

After getting the version of Microsoft Excel on your desktop/PC then download Power Query of the same version.

## Installation
After the downloading the  Power Query then you have to install it and it will be automatically added to your Microsoft Excel program. Just check on the ribbon tab called Power Query will be added.

![Power Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fyc3j9k7yp5q10kujq6k.PNG)

## Getting started
I will use the data files of the Cookies Company that have the data for the year 2017,2018,2019,and 2020 then combine them in one spreadsheet.


Download the Data file through the link below
[Cookies Data File](https://drive.google.com/drive/folders/13bq0mbjBrvlNeG-T8IihKPBcFfWVHU2N?usp=share_link)


## Open Power Query
![Open Power Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ydcumehngvk39tgaleas.PNG)
After Opening Power Query for those who use Ms. Excel 2010 and 2013 but for others just check on the Data Tab on Ms. Excel there will be Get and Transform option.

## Upload the Data Folder
Upload the folder through import file option, where there are other options including the upload folder.

![Upload Folder](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bgzwuxybaalxpu22mszr.PNG)

## Combine and Transform
After selecting the folder then what follows is to combine all the different selling of cookies in one year also transform or process and clean the data.

![combine](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gjoloz4w2m2yzcowpext.PNG)


## select sample data


![Sample data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aj08tliazrf11ub60j67.PNG)
![sample](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f9dpqzlawjc9w6982ctk.PNG)

After simple editing, you just combine and load that will lead us to the Microsoft Excel program where the sheets will be combined together.


![Final combination](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/faf8ae8csq1ozl4l55n0.PNG)