Maintain a price book in #Excel with #PowerQuery

Feel the Power (Query)

This is my first post devoting to my recent love for Excel – Power Query.  🙂

First, let me make myself clear.  I am still learning Power Query (indeed I am still learning Excel too).  The more I learn and use Power Query, the more Excel power I get.  Frankly, I am far away from being an expert in Power Query.  Having said that, I am “relatively” good by knowing how to use many Magical commands through the super-friendly User Interface of Power Query.  Together with a little knowledge in editing auto-generated codes in formula bar of Query Editor, I feel like I can fly with Excel.  🙂

In this post, I am trying to show you how to solve a workplace problem, that is quite complicated if using Excel alone.

Warning: This is a long post.

The routine task

Imagine you need to maintain 8 price books (for 8 different markets) with more than 10,000 SKUs each.   From time to time, price of certain SKUs changes.  When it happens, you need to send the list of SKUs with new prices and effective dates to individual markets so as to provide them the information required for loading to local POS system.

To achieve that, you create 8 separate tables, listing all SKUs with coming price changes only.

The issue

As time goes by, there are so many tables with “new” prices; and “new” prices become “old” prices and obsolete.  Worst still, there is no Master Price Book showing the prices of each SKUs in different markets.

Because of this, you may need to go through each price book one by one in order to find the latest/coming price (or historical prices) of a particular SKU in a particular market.

My suggestion

When I heard about this (a real workplace case), I asked my colleague: “Why don’t you consolidate all data into a Master Price Book?”

She responded in doubt…… (skipping the details here. ;p)

…… I quickly drafted the data table below:

Excel Tip - Price Book with PQ

and said: “You may determine the Status of an item in a market based on the Effective Date, with the field “Status” you may get the information you need by Pivot Table easily”

A moment of silence…

Then she said something like… “It’s difficult and time consuming to update the status (manually)…”

Arrrrh…. I realized her concern which is so true:   She thought the “Status” needs to be maintained manually……

 

With Power Query, this is achievable with ease

Let’s not worry about how to combine the eight different files, as that is pretty easy with Power Query… ;p and this is not the topic of this post.

Our Objective

Based on the following dataset, assign three different status (“Current“, “Coming“, “Inactive“) to each row of the data.

Excel Tip - Price Book with PQ

The logic is simple, when a record with effective date of a SKU in a market is:

  • immediate future ==> its status is Coming
  • immediate past or today ==> its status is Current
  • Others ==> Inactive

An example below (assume today is 5/26/2018):

Excel Tip - Price Book with PQ0.1

Make sense?

If you are formula expert, you may be able to figure out an array formula to return the status of a SKU in a market based on the effective date.  However it is super complicated to write such an array formula to most of regular users.  And also think about the scale of data volume (>80,000 rows of record).  Array formula to return the status may not be an efficient approach.

 

Let’s do it the Power Query way

Note: All screenshots are prepared using Excel 365, which Power Query is renamed as Get and Transform, under the Data tab.  If you are using Power Query for Excel 2010 or 2013, you may find the icons at different locations on your Ribbon, where Power Query has its own tab.  Nevertheless, the flow should be more or less the same.

You may download a Sample File to follow along.

1. Turn the data range into Excel Table

  1. Select the data range –> Go to Insert tab
  2. Table  (Shortcut: Ctrl+T)
  3. Confirm the data range; and tell Excel if “My table has headers”
  4. OK

Excel Tip - Price Book with PQ1.0

As a good practice, let’s give a more meaningful Table Name than “Table1”, e.g. RawData

Excel Tip - Price Book with PQ1.1

2. Load the Table to Power Query Editor

  1. Select any cell of the Table you want to load to Power Query –> Go to Data Tab
  2. From Table (Note: if your data range is not yet converted as Table, Excel will do it for you.)

Excel Tip - Price Book with PQ2.0

 

Now the table RawData is loaded to the Power Query Editor.  The following screenshot shows a preview of data (as our data set is small here, you see all rows of data in the preview).

Let’s rename the query to “BaseQuery”

  1. Select the Query on the left pane –> Right-Click –> Rename or Change the Name directly from the right pane (Query Settings)
  2. The step “Changed Type” is automatically generated; let’s delete this step by clicking the “x”  (Look at the preview data, did you see the data type for SKU changed to Number, which turns “00100” into “100” –> Something we don’t want…)

Excel Tip - Price Book with PQ2.1

Let’s define the data type

Excel Tip - Price Book with PQ2.2

By clicking on the “ABC 123” icon on the left of column header, you will see a dropdown list of different data types for selection, which is quite intuitive: Excel Tip - Price Book with PQ2.21

Excel Tip - Price Book with PQ2.3

(Tip: We may apply the same data type to multiple columns by holding Ctrl key when selecting columns; remain holding the Ctrl key when clicking the “ABC 123” icon)

Let’s set the data type for each column as below:

  1. Market / SKU / Product Cat as Text
  2. RSP as Currency
  3. Effective Date as Date

Now we have our BaseQuery set and ready.  We are going to reference this query for future steps.

 

3. Create a query for Current Price

First, let’s reference the BaseQuery to create a new query:

Right-click the “BaseQuery” on the left pane –> Reference

Excel Tip - Price Book with PQ3.0

Now we have “BaseQuery (2)” which is identical to the BaseQuery.  Let’s rename it to “CurrentPriceList

Excel Tip - Price Book with PQ3.1

Keep only the columns we need

We will need three attributes (which are Market, SKU, and Effective Date) to define the price status of an item in a market, hence we can “Remove Other Columns

Excel Tip - Price Book with PQ3.2

Let’s take a look at the formula bar.  For each action we did, Power Query generated a formula automatically.

Excel Tip - Price Book with PQ3.3

 

Do you remember the logic of determining a price status?  For an item with more than one effective date, the one with the immediate past date (on / before today) is the Current price.  Make sense?

Let’s filter only effective dates on or before today:

Excel Tip - Price Book with PQ3.4

  1. Select “is before or equal to”  (if the effective date is today, then its status is “Current”)
  2. Click on the “Calendar” icon
  3. Select “Today”
  4. OK

Excel Tip - Price Book with PQ3.5

Now, let’s have a look at the formula bar.

The filter is applied towards a “hard-code” date, which is 2018/5/26… No good.

Excel Tip - Price Book with PQ3.6

As an Excel guy, I tried to change that portion to “Today()”… but it did not work as the language behind Power Query is different.  There is no such Today function in Power Query.

The twist is to replace the #date(2018, 5, 26) with

Date.From(DateTime.LocalNow())

And it works perfectly. 🙂

Now, examine the filtered data.  What we want is the list of items with “Current” status, which are highlighted below:Excel Tip - Price Book with PQ4.0

In natural language, we want to get the latest (biggest) Effective Date of a SKU in a market.

We can do it with Group By in Power Query:

  1. Go to Home tab (of the ribbon on Query Editor, not the Excel)
  2. Group by
  3. Select “Advanced”
  4. Group by Market, then Group by SKU (note: You may need to “Add grouping”)
  5. Define a new column name –> Effective Date
  6. Select Max under Operation (because we want to get the latest effective date)
  7. Select “Effective Date” under Column, where we want the operation be applied to
  8. OK

Excel Tip - Price Book with PQ4.1

Here we go!  A list of SKU with the latest Effective Date that is on or before Today.

Excel Tip - Price Book with PQ4.2

Let’s add a column to give them a proper marker.

  1. Go to “Add Column” tab
  2. Click Custom Column
  3. Name the new column as “Status
  4. input = “Current”
  5. OK

Excel Tip - Price Book with PQ4.3

Don’t forget the change the data type to “Text” for the resulting new column just added.Excel Tip - Price Book with PQ4.4Excel Tip - Price Book with PQ4.5

 

4. Create a query for Coming Price (i.e. the next future price)

First step is to “Reference” to the BaseQuery (again):

Excel Tip - Price Book with PQ5.0

The steps afterward is 99% the same as the steps in Section 3.  So it’s better to illustrate through a screen-cast:

Excel Tip - Price Book with PQ5.0

Did you time it?  It was done in less than 80 seconds.  🙂

Note the two major differences:

  1. As we want to determine coming price, we filtered “>” Today instead of “<=
  2. To get the next effective date coming, we use “Min” for the operation in the steps for Group By.

Excel Tip - Price Book with PQ5.1

Excel Tip - Price Book with PQ5.2

5. Append the two Queries

So far, we have created two queries: One contains list of items with Current status; one contains list of items with Coming status.

Excel Tip - Price Book with PQ6.0Excel Tip - Price Book with PQ6.1

We want to feed this ” newly added” status to RawData.  Before we do so, let’s put the two queries into a single query :

  1. Go to Home tab
  2. Append Queries
  3. Append Queries as New

Excel Tip - Price Book with PQ6.2

In our case, we have only Two tables to append.

  1. Select “Two tables
  2. Select CurrentPriceList as Primary table
  3. Select FuturePriceList as “Table to append to the primary table”
  4. OK

Excel Tip - Price Book with PQ6.3

Here we go!  An appended table showing list of items with status of both Current and Coming.

Excel Tip - Price Book with PQ6.4

6. Feed the status back… by Merge Queries

First step is to “Reference” to the BaseQuery (again).

Excel Tip - Price Book with PQ7.0

This time, I renamed it as f_FinalTable.  This will be the Table/Query we used for Pivot Table after all.

Excel Tip - Price Book with PQ7.1

To Merge the queries

  1. Select the f_FinalTable on the left pane
  2. Go to Home tab
  3. Merge Queries

Excel Tip - Price Book with PQ7.2

  • As we started with f_FinalTable, it is put on the top as “Left” table.
  • Now select the Table (Append1) we want to merge with (consider this is a lookup table in Excel):
  • Join Kind is set to “Left Outer” by default.  Leave it there as it is.

Excel Tip - Price Book with PQ7.3

 

Now let’s select the matching columns on both tables.  Note, sequence does matter. Please be careful.  The following screen-cast demonstrates the flow (note: Hold Ctrl key when selecting columns):

Excel Tip - Price Book with PQ7.4

When the matching columns are selected, we will see a summary at the bottom showing number of matched records.

Technically, it is a VLOOKUP with three lookup values.  You should know how tedious it is with regular Excel.

Now, we see a new column called “Append1”, the query name we just merged.

 

Let’s continue by clicking the “expand” icon:

Excel Tip - Price Book with PQ7.4

Check the field that we want.  In our case, “Status“.  Tip: Unckeck the “Use original column name as prefix”.

Excel Tip - Price Book with PQ7.5

 

VLOOKUP with three values done successfully, with ease!

Excel Tip - Price Book with PQ7.6

Wait… What “null” means?

For those non-matching records, null is returned.  What are the non-matching records? They are record of neither “Current” nor “Coming”.  In other words, they are “Inactive“.

arrrrrh.  That’s right.

Let’s replace “null” with “Inactive”.

  1. Select the column “Status” in the f_FinalTable query
  2. Go to Home tab
  3. Replace Values
  4. Value to Find: null, Replace with: Inactive
  5. OK

Excel Tip - Price Book with PQ7.7

wakaka… it’s done in a flash. 🙂

To get a better view, let’s sort the result by 1) Market, 2) SKU, 3) Effective Date.  Then click Close & Load.

Excel Tip - Price Book with PQ7.8

 

Result

Each query created is loaded to different worksheets.  And you will see a “Workbook Queries” pane on the right.  Click on a Query to go to the table loaded:

Excel Tip - Price Book with PQ9

 

The true Power

If you have been following along, you may be thinking… well it takes quite many steps and they are not easy at all…  Is it worth doing so in this way?

Remember, we are dealing with a big table with more than 80k rows of record.  The table needs to be updated on regular basis.  Every time we update the price of certain SKUs (quite a lot indeed), we want to retrieve updated information easily (and quickly, means in minute not hour) to show list of current / coming price with effective date by market.  It’s not an easy task with regular Excel.

With Power Query, and the steps we created, this tedious task becomes a click of Refresh.

Here’s the sample file with the queries: Sample File – Maintain Price Book Using Power Query (Finished)   (Please Enable Content when prompted) 

Let’s see the Power in action.

Suppose we have new data added to RawData.

Excel Tip - Price Book with PQ9

To get the updated information, simply go the the query loaded, right-click and Refresh.  As simple as this!

Excel Tip - Price Book with PQ9.1

Isn’t it easy but powerful?

The following diagram illustrates the flow of the queries created.  Simply awesome!

Excel Tip - Price Book with PQ10.3

Without a single formula on Excel worksheet, we have just achieved something amazing. Most actions performed in Power Query Editor were Click and Select, with just one modification in formula bar to fix the hard-coded date filtering (which may require a bit  of Googling).  And the result is totally Refreshable when data is updated / changed.

Of course, we need to examine the data pattern and think about the logic required to achieve the end result, which require some serious thoughts.  Nonetheless, we will need to do the same via regular Excel approach.

I always tell my friends that if you can manage all the commands from Power Query’s User Interface, you are already a SUPER POWER User of Excel.  Unfortunately, they have no idea of what I am talking about, without seeing it.  That’s the motivation of this post.

 

Extra

Well, we have already turned a time-consuming task that may take up hours of work (if not days) into seconds.  We should be happy and complain no more.  Nevertheless, there are still rooms for improvement in terms of efficiency.

Do you remember we have modified the steps of Filtered Rows as below?

Excel Tip - Price Book with PQ10.1

Excel Tip - Price Book with PQ10.2

You can imagine, the evaluation of getting today takes place to each row.  We knew that in Excel, we would put TODAY() in a cell, say A1, then reference to A1 for today wherever needed.  In this way, TODAY() gets evaluated once only.

Following the same logic, we would like to revise the formula into something like:

Excel Tip - Price Book with PQ10

However it would not work until we have created a query to retrieve today.

To achieve this, we start with a Blank Query, Type the following formula, Rename the Query as Today.

=Date.From(DateTime.LocalNow())

Excel Tip - Price Book with PQ10.4

Did you notice that in the Sample File – Maintain Price Book Using Power Query (Finished), I have already created “Today” for you, so that you may revise the steps of Filtered Row directly.  🙂

How efficiency it is?

For a small dataset in our example, it makes no noticeable difference.  For a dataset of 200k rows of record, the refresh time run on my computer was about 15s.  With the slight improvement, the refresh time reduced to ~8s.  So why not?!

 

EndNote:

If you are new to Power Query, you may not fully understand the steps list above thoroughly.  That’s ok.  The purpose of this post is to show you what Power Query can achieve instead of giving a fully-explained tutorial.  Hopefully this post will arise your interests (and curiosity) to learn more about Power Query.

If you are expert in Power Query, I believe you may have more advanced ways of using M code to solve the problem.  If you have suggested solution in other approach(es), please share in comments.  🙂

Advertisements
This entry was posted in Power Query and tagged , , . Bookmark the permalink.

2 Responses to Maintain a price book in #Excel with #PowerQuery

  1. Sandeep Kothari says:

    This is great!

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s