Power
Query:
Basic
Example
Query to self update for
new data coming in
By Paulo Coelho Nov_22
Imagine having sales figures monthly communicated to you for some cleansing and respective
power pivots. We have currently 3 files worth of figures and we want to avoid having to repeat the
cleansing procedure every time we get a new file.
So, firstly we will take a peak on the files we get and where they will get stored:
We can already see that some cleansing will be required such as: Separate Customer ID from
Customer Name; change Type of Units Shipped; Ignore N/a from any calculations; add some new
columns such as Profit and Days to Ship (to measure Customer Service SLA). First, lets add these
files onto Power Query using a new Excel file:
- Define Folder from where we need to GET DATA from (where the existing files are located)
Next you will see all 3 files inside the respective folder which we will need to Combine &
Transform Data. Combining will bring all 3 datasets together and allow the user to format columns
across all files:
Next this screen will appear giving you a preview of combined data from the common tab in each
file (Order Data). We will proceed by selecting OK opening the Power Query:
Welcome to Power Query. This will be the screen Excel will load so we can manage the datasets,
cleanse and load it back to Excel. A quick mention related to the right hand-side steps; this is like
a macro where all steps executed will be “recorded” here. At any point you can remove a step or
change its order however you like it.
First lets eliminate the 1st You saw from the Preview before data Import that the
column which holds on dataset had N/a as values. We do not want to consider
informational value. Right click these (you can confirm with business if they choose to
on Source.Name and Select replace the values but for this exercise we shall ignore
Remove Column: them. Using the filter on the Order ID column and
Unselect N/a:
Now lets change the Column Type of Revenue and Cost to Currency and Add a New column to
create Profit:
- Select Revenue and HOLDING CTRL Select Cost
- Right click on any of the selected columns; select Change Type and Currency
- Select Revenue and HOLDING CTRL Select Cost
- Select Add Column, Standard, Subtract so it subtracts the Cost from the Revenue on the new
column
- Rename the column, call it Profit
- Then you can drag the column where you best find it suited such as next to Cost
Lets separate the Customer ID from Customer Name:
- Select Customer ID Column and right click it
- Select Split Column and By Delimiter (our delimiter will be the “ – “)
- Ensure you fill the “ – “ under Custom and click OK
You can now rename the columns accordingly:
- Rename Customer ID
- Rename Customer Name
Finally, lets create a new column to give us the Total Days to Ship between the Order Date and
Delivery Date:
- Select Delivery Date first and then Order Date columns
- Select Add Column, Date and Subtract Days
- After, rename the new column accordingly
We are now ready to Load it back to Excel. Click Home, Close & Load. We will see the end result
with all data cleansed, new data columns added and all files merged or combined.
Finally we can build a quick Power Pivot and add the next months data so we can see how much
work you have just saved.
- Create a Power Pivot and a graphic
Finally we can build a quick Power Pivot and add the next months data so we can see how much
work you have just saved.
- Create a Power Pivot and a graphic
Finally we can build a quick Power Pivot and add the next months data so we can see how much
work you have just saved.
- Add a new Sales Files (for 2022 - current) onto the Windows Folder
- Excel -> Data -> Refresh All
- Check your Pivot Table getting updated!
Thank you and
HAPPY Excelling! ☺
