Power Query is an excel Add-in which allows you to connect wide variety of data sources. Power Query has an intelligence to find a data set on a webpage. Once you have the data set you may notice that is not in right shape. Using Power Query you can combine data sets using join, merge operations. Power Query generates the script when you are performing the steps in loading and cleansing the data and later that script can refreshed with a button click. The Power Query can be downloaded from here.
Now using Power Pivot you can feed this dataset into data model. Power Pivot creates an in-memory BI semantic model within excel.
Once the model is ready then you can push it to Analysis services or SharePoint or Power BI. When you refresh the Power Pivot model then it all applies the transformations.Power Query supports following data sources to import data into excel
This post explains how to load the data from CSV, Choose From File and select From CSV. The loaded data in query editor looks as below
The various options that you can use to mash-up the data are like removing the unwanted columns and duplicate values. Other options like Group by, pivoting and renaming the columns can be used on data.
You can split the columns with a delimiter
Changing the data type of a column is easy
Using the Group By in Power Query, Select the columns that you want to group by in query editor and say Group by and it then adds a new column with values
Power Query records all the steps that you performed on data
Once the transformations are complete on data then you can say done then it will loads the data into a sheet. Now you can load this data into a model
once the data is Power Pivot then click the Manage button there you can add additional columns , create relation ships among datasets and aggregations etc. You can write DAX(Data Analysis Expressions) expressions to add additional columns in model
You can create relationships among data sets in diagram view
You can use LookupValues DAX function to search a particular column in other data set
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)
This post explained only few expressions, complete reference can be found here
