What is Power Query in excel
Power Query in excel helps in connecting multiple sources and sizes of data. It helps in defining an automated process while connecting the first time and this can be used multiple times to save time and resources.
Excel Power Query is a business intelligence tool to perform extract, transform and load functions in Excel/Power BI. It has a graphical interface to load data and Power Query Editor helps in transforming data in a desirable way.
How to use power query in Excel
To use power query in excel we need to go in ribbon and select Data option. We will get first options as Get & Transform Data. In this option we get option Get Data which has more options to get data from multiple sources as shown in second image.
We can choose any option to get data from the given source.
In this example, we can use csv to understand how Power Query works. Usually, CSV and text are most used options to load data in excel.
It will ask us to take to the folder from where we want data file to connect.
By following the above steps we will get our data, which now needs to transform(using Transform Data) or can be directly load(Load option) into the excel file. We will try Transform Data option to understand it better.
We will get options to transform our data. There are multiple tabs to transform the data. We can remove undesirable columns and can change columns data types.
After transforming we will load the data in excel. There are two options to load the data. The first option directly loads the data to excel and the second option gives us an option like below to load to a specific cell where data needs to load.
That’s all for this blog. I Will elaborate on Excel Power Query further in different blogs.
You can read more on power query excel from the below article.