Category: POWER QUERY + POWERPIVOT

Determine the lowest bidding vendor(s) for each product in a Pivot Table

{0 Comments}

Imagine a dataset like this.  This dataset shows vendors that submitted proposals for supplying various parts to a Company.  There is one column for each of the twelve months.

untitled

Via a simple Pivot Table, one can determine the lowest bidding vendor per product (part) for any chosen month.  However, one may also want to know the names of those vendors for each product (as seen in column G below).  Notice, that Vendor 2 and Vendor 3 submitted the lowest bid for Product 1 and therefore both names should appear in the result.

untitled

I have solved this problem using PowerPivot and Power Query a.k.a. Data > Get & Transform in Excel 2016.  You may download my solution workbook from here.

Show sales only for corresponding months in prior years

{0 Comments}

Refer to this simple Sales dataset

untitled

The objective is to create a simple matrix with months in the row labels, years in the column labels and sales figures in the value area section.  The twist in the question is that for years prior to the current year (2018 in this dataset), sales should only appear till the month for which there is data for the current year.  For e.g., for 2018, data is only till Month 4 and therefore for prior years as well, data should only appear till Month 4.  As and when Sales data gets added below row 17, data for prior years should also go up to that month.

The expected result is

untitled1

You may download my PBI file from here. The same solution can be obtained in Excel as well (using Power Query and PowerPivot).

Filtering on 2 date fields within one Table

{0 Comments}

This table contains a list of all the inspections created and completed within different time periods.

untitled

The objective is to create two Tables from this single table - one showing the Accounts created within the chosen time period and another showing the those that were closed within the same time period.  Here are screenshots of the expected results.

untitled1 untitled2 untitled3 untitled4

You may download my PowerBI desktop solution workbook from here.  The same solution can be obtained in Excel as well (using Power Query and PowerPivot).

Determine number of learners who have completed different stages of multiple online courses

{4 Comments}

Here is a sample dataset of learners who have cleared different stages of multiple courses on offer within an Organisation:

Learner Stage completed Course
Bill Stage 1 Public Speaking
Bill Stage 2 Public Speaking
Bill Stage 3 Public Speaking
Susan Stage 1 Effective Communication
Bob Stage 1 Public Speaking
Bob Stage 2 Public Speaking
Sheila Stage 1 Effective Communication
Sheila Stage 2 Effective Communication
Sheila Stage 3 Effective Communication
Frank Stage 1 Effective Communication
Frank Stage 2 Effective Communication
Henry Stage 1 Public Speaking
Henry Stage 2 Public Speaking
Bill Stage 1 Effective Communication
Bill Stage 2 Effective Communication

From this sample dataset, one may want to know how many participants have completed each stage of these multiple courses.  The expected result is shown below:

Row Labels Stage 1 Stage 2 Stage 3
Effective Communication 1 2 1
Public Speaking 2 1
Grand Total 1 3 2

In this workbook, I have shared 2 solutions - one using formulas and the other using the Power Query & PowerPivot.

Compute product wise YTD Revenue from a matrix like/Cross tabular dataset

{0 Comments}

Assume the following data layout

Name Budget April Actual April Budget May Actual May Budget June Actual June
a 2 1 1 4 3
b 4 4 2 1
c 2 3 3 3
d 2 1
e 5 6 4 8 6

As one can observe here, there are two sub columns for each month - Budget and Actual.  From this data layout, we wish to compute the Product wise YTD budget and Actual sales figure.  So for example, if one selects May in a drop down, then the YTD budget for Product B should be 6 and YTD actual for Product B should be 5.

The expected solution should look like this

Month Name May
Products YTD Budget YTD Actual
a 2 2
b 6 5
c 2 3
e 9 14
Grand Total 19 24

You may download my solution workbook from this link.

You may watch a short video of my solution here

Compute potential Sales of a retail outlet

{8 Comments}

The objective is to assist a Store Manager with computing potential sales across different products and colours.  To start with let's assume two datasets:

1. Customer-Colour dataset - a two column table which lists down the colour preference of each customer; and
2. Colour-Product-Price dataset - a three column table which lists down the multiple products associated with each colour and the associated prices

My objective is to compute the "Potential Sales" if each customer buys one unit of each colour of each product.  While this can be resolved by using formulas (see Formula solution worksheet of the workbook), I'd like to resolve this problem with the help of the Power Query and PowerPivot tools.

The initial challenge will be to establish a relationship between the two tables because one cannot establish Many to Many relationships in a PowerPivot.  The two tables above are a perfect example of Many to Many relationships because each customer likes multiple colours and each colour is associated with multiple products.

You may download my solution workbook from this link.

You may also view a short video of my solution here:

Perform a Competitor, Feature and Customer Analysis with the PowerPivot

{2 Comments}

Assume there are four interrelated tables. One may want to create a pivot that allows one to filter data by using the slicers. Data should be filtered by the following interdependent slicers selections: Customer, Country and segment.  The logic behind the pivot when using the slicers shall be as follows:

1. Feature N is only shown if relevant to Customer X in Segment Y and Competitors do not possess Feature N
2. Competitor X is only shown if Competitor X exists and is active in Country Z and is relevant to Customer Y in Country Z

So after slicer selections are made, the idea is to display all features that one can offer and are relevant to the respective customer in the respective segment and country, regardless of whether the competitors can offer them or not.  So if one competes with competitor 1 in a specific project and offers features 1, 3, 7, offering the very same features to our shared customer does not make sense.  The customer won't see a benefit in choosing me over competitor 1.

Here's an elaborate example:

1. If one selects Customer 1/Segment A/Country 1 from the three slicers, then the Pivot Table should display as follows:

1. Row Labels - Display features in pivot if they are implemented or relevant.  The ones in Blue are implemented and the ones in green are relevant.  The pivot now shows that competitor 1 does not have features 2 and 9 which. Thus one's sales pitch will focus on offering features 2 and 9.  This section should also show data for Feature benefit calculation.

2. Column labels - Competitor 1 and 2 are displayed in the Pivot Table because they are both relevant in Country 1.

3. Competitor has/does not have feature (Value area section) - The following competitors have the same features I can offer my customer 1 in segment A:

  • Competitor 1 has features           1, 3, 7
  • Competitor 2 has features           3

Therefore they are marked with an “x” whenever they have the features.

The other competitors also have features that I offer to customer 1 in segment A, but since they are not active in Country 1, they are automatically not displayed when using the slicers.

You may refer to my solution in this workbook.

You may also view a video of my Power Query solution here:

Compute attrition rate from two different data sources

{0 Comments}

Assume two databases:

1. One showing employee headcount (one row per employee) which has all employee details such as Name, ID, Date of Joining, Supervisor name, Department etc. (Range A1:R781 of Source worksheet)

2. The other showing data for employees who resigned. (Range U1:Z36 of Source worksheet)

The task is to compute the attrition rate for selected Group and selected months.  Groups and months will be selected from slicers.

In the attached workbook, one can see the aborted Pivot Table attempt and the successful PowerPivot solution.  Refer to cell I25 of Abortive Pivot Table attempt worksheet to see how attrition rate should be computed.

Story telling with Excel Power BI

{6 Comments}

With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations.  Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story.  To be able to interact with/create visualizations, you will need to install Microsoft Office Professional Plus 2013 (this version will already have two of the four components of Power BI - PowerPivot and Power View).  Additionally, you will have to install the following add-ins from Microsoft (the other two components of Power BI)

1. Power Query; and
2. Power Map

I have tried to showcase the prowess of Power BI tools of Excel 2013 in these two workbooks:

1. An overview of the BRIC Economies
2. Sales data analysis

You may watch a video of my work at this link

Create a Pivot Table from multiple worksheets in different workbooks

{65 Comments}

Assume two workbooks named Book1.xls and Book2.xls.  In Book1.xls, there is a sheet named Jan and in the Book2.xls, there is a worksheet named Feb.  In both the worksheets - Jan and Feb, the following exist

1. Same number of columns on both worksheets; and
2. Same order of columns on both worksheets; and
3. Same spellings of headings on both worksheets

As you can observe in zipped file, all three aspects mentioned above are in place.  One may want to perform any of the following tasks:

1. Create one pivot table from both worksheets (which are in different workbooks); and/or
2. Consolidate data from both worksheets (which are in different workbooks) one below the other

To curtail the workbook size, I have deleted rows of data.  In each workbook, there were 65,000 rows of data on each Jan worksheet and Feb worksheet.

While one simple way would be to copy and paste data from Jan and Feb worksheet in a third worksheet and then create a pivot table, the following shortcomings exist with this method

1. Copying and pasting data from multiple workbooks into one is a manual process; and
2. Since the pivot table will be based on the manually created sheet (by copying and pasting), changes in the two original workbooks will not update the pivot table on refreshing unless the same changes are carried out in the consolidated sheet (created by copying and pasting above) as well.  So this leads to duplication of effort.

Both problems outlined above can be resolved by using MS Query.  The technique mentioned below will work as long as the three conditions mentioned above are satisfied.  Furthermore, since MS Query will only recognize named ranged with rows up to 65,536, the number of rows of data in each of the worksheets (Jan and Feb) should not exceed 65,536.  The combined rows in both worksheets could be any number.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  In order for the Power Query solution to work, all files will have to be saved as CSV files in the same folder (Since a CSV files can have only one worksheet, the Jan and Feb worksheets in the other workbook will have to become two workbooks - Jan.csv and Feb.csv).  In Power Query, there is a feature to append data from multiple CSV files lying in a specific folder into one running range.  Once this is done, the running range can either be transferred to an MS Excel worksheet (if the number or rows are up to 1 million) from where a Pivot Table can be created OR the running range can be loaded to the Data Model (Excel 2013) [the Data Model will be able to accommodate more than 1 million rows depending upon the hardware and software configuration of the machine] from where a Pivot Table can be created.

You may watch a short video here:

Solution for all versions of MS Excel

The steps for creating a pivot table from multiple worksheets (both in the same file) are:

1. Both workbooks - Book1.xls and Book2.xls are saved on the desktop.
2. Open Book1.xls, select the data on the Jan sheet (including the first row as the header row - on the Jan sheet, it will be A1:S4.  Ensure that the header row has some distinctive formatting such as Bold or some colour) and press Ctrl+F3 > New.  In the Name box, type Dummy and click on OK > Close.
3. To cross check that the name assigned above has indeed been assigned correctly, select the data range once again and in the Name box (left of the formula bar), Dummy should appear.
4. Select A1:S4 of the Jan sheet again and press Ctrl+T to convert this range into a Table.  Ensure that the "My Table has headers" box is checked.  Save and close Book1.xks
5. Repeat steps 2 - 4 for the Feb sheet on Book2.xls as well.  In step 3, just change the name to Dummy1.  Save and close Book2.xls
6. Open a new workbook and go to Data > From Other Sources > From Microsoft Query
7. Under Databases, select Excel files > OK
8. In the Directories dialog box, navigate to the folder on the desktop where the Book1.xls file is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and double click on the folder where Book1.xls file is saved
9. In the left hand side window, select the Book1.xls file and click on OK
10. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box
11. Click on Next three times
12. Select the option of Return Data to Microsoft Excel
13. At this stage, if you wish to get data in a Table form then select Table.  If you wish to create a pivot table, select the second option button - Pivot Table.  Select any cell where you would like to the result to appear, say cell A1.
14. Click on OK.  A counter will run at the bottom left hand side with the title of Reading Data
15. If you had selected pivot table in step 13 above, then the pivot table grid/layout will appear
16. You may now drag fields to create a pivot table

So the Table or pivot table which you have created so far is only from Jan sheet of Book1.xls.  We have to also include data from Feb sheet of Book2.xls.

17. If you had created a Table in step 13 above, then click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.  If you had created a Pivot Table in step 13 above, then select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.
18. In the Command text box, you see something like this

SELECT dummy.Invoice_number, dummy.Date, dummy.`Article Number`, dummy.`Article Name`, dummy.`Tax Type`, dummy.Brand, dummy.Category, dummy.`Sports Code`, dummy.`Product Type`, dummy.MRP, dummy.`Customer #`, dummy.`Customer Name`, dummy.Region, dummy.`Area Code`, dummy.Quantity, dummy.`Line Discount`, dummy.`Sales Value`, dummy.`Before Tax`, dummy.`VAT Amount` FROM `C:\Users\Ashish\Desktop\Book1.xls`.dummy dummy

Replace this lengthy statement with the following shorter one

SELECT * from `C:\Users\Ashish\Desktop\Book1.xls`.dummy
Union all
SELECT * from `C:\Users\Ashish\Desktop\Book2.xls`.dummy1

19. Click on OK and the Table/Pivot Table should update
20. Save the file as Consolidated.xls and close it.

Update Table/Pivot Table for addition/editing in existing named ranges

To see the effect of a dynamic Table/pivot table, edit data in Jan and/or Feb sheets of Book1.xls and/or Book2.xls.  Save and close the file(s).  Open Consolidated.xls and refresh the Table/Pivot Table (Right click and Refresh).  At the bottom right had side the counter will run again and (step 14 above) and once it has read all data, the Table/pivot table will update.

Furthermore, since the two named ranges (Dummy and Dummy1) have been converted into Tables, even if you add data by rows (with no row being left entirely blank), when you right click to Refresh the pivot Table, data of new rows will appear.

Update Table/Pivot Table for addition of new worksheets

Assume you now want to add data from a worksheet titled March in Book3.xls.  To include this sheet in the pivot Table, follow the under mentioned steps:

1. Open Book3.xls

2. Follow steps 2 - 4 mentioned above for the March sheet.  In step 3, just change the name to Dummy3

3. Save and close the workbook

4. Open Consolidated.xls

5. Select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.  If you selected Table (instead of Pivot Table) in step 13 above, click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.

6. Click on the Definition tab and under Command Text, add the following at the end of the SQL query:

Union all
SELECT * from `C:\Users\Ashish\Desktop\Book3.xls`.dummy3

7. When you click on OK, the counter will run again and the pivot should reflect data from March sheet.