• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to get "Best of Network" column using PowerQuery|Power Query|Excel Forum|My Online Training Hub

You are here: Home / How to get "Best of Network" column using PowerQuery|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryHow to get "Best of Network" column…
sp_PrintTopic sp_TopicIcon
How to get "Best of Network" column using PowerQuery
Avatar
Salman Baig
Member
Members
Level 0
Forum Posts: 10
Member Since:
May 16, 2020
sp_UserOfflineSmall Offline
1
May 16, 2020 - 4:17 am
sp_Permalink sp_Print

Hi,

Image Enlarger

With reference to the above image. Please help with adding "Best of Network" column using power query.

Let me brief you about what I am actually trying to achieve

I have customer data from different locations and I am trying to fill out the table above using power pivot. For data transformation, I am using Power query.

The words Served, -ve impact, DR% and Sigma level are numerical values which I would be needing in the above mentioned format. I was able to get values for location HYD and Rest of Network (While data transformation in power query - by creating a new column named Rest of Network and using if statements to fill it with "others" if location is not HYD).

But I am unable to figure out how to get the best performing location.

Let's say we have locations 1,2,3,4,5 and location 3 is the best performing one, then I would need Served, -ve impact, DR% and Sigma level values for location 3.

I thought of using the min() function but it was a lot of manual work. Therefore, I am seeking help here.

Can this be done in power query so that I can have everything in a single pivot table

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
May 16, 2020 - 9:24 am
sp_Permalink sp_Print

Hi Salman,

Please upload the image again, it didn't work. Note: an Excel sample file would be better than an image, if you can.

Mynda

Avatar
Salman Baig
Member
Members
Level 0
Forum Posts: 10
Member Since:
May 16, 2020
sp_UserOfflineSmall Offline
3
May 16, 2020 - 11:08 pm
sp_Permalink sp_Print

Hi Mynda,

I have attached an excel sample file with inputs on what exactly I am looking for. Any help would highly be appreciated.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
May 17, 2020 - 9:58 pm
sp_Permalink sp_Print

Hi Salman,

Thanks for sharing your file, however I'm not understanding where you got the figures for the 'Best of Network' in column P of the PivotTable. They don't appear to reconcile to the figures in your table, so the example is difficult to understand and work back to the source data.

Also, you're using terms familiar to you, but not to me e.g. DR%. Please explain how you would calculate the Best of Network figures using plain English and with reference to your example data so I can follow the audit trail.

Thanks,

Mynda

Avatar
Salman Baig
Member
Members
Level 0
Forum Posts: 10
Member Since:
May 16, 2020
sp_UserOfflineSmall Offline
5
May 19, 2020 - 1:15 am
sp_Permalink sp_Print

Hi Mynda,

Thank you for the reply and apologies for creating that confusion. Kindly ignore the jargons (Served, -ve impact, DR% and Sigma level).

In the sample excel sheet attached, I have used some common terms like product sales and service sales.

Earlier I have just randomly added values to the "Best of network" column (P). However, now I have corrected it and added the actual values using a separate pivot table.

Thank you

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
May 19, 2020 - 10:38 am
sp_Permalink sp_Print

Hi Salman,

Thanks for clarifying. There are a couple of issues/questions:

1. You can't do this with a regular PivotTable. You'd need to write a Power Pivot DAX measure. Do you have a version of Excel with Power Pivot?

2. You can't have the layout as you displayed in a PivotTable. i.e. You're showing the 'best of network' values as a column label, but it would need to be a measure and shown in the row labels with the other measures (sum of product sales and sum of service sales).

3. You haven't said whether you'll ever want the Best of Network figure broken down by month, like you have for HYD and Rest of Network.

Mynda

Avatar
Salman Baig
Member
Members
Level 0
Forum Posts: 10
Member Since:
May 16, 2020
sp_UserOfflineSmall Offline
7
May 19, 2020 - 9:12 pm
sp_Permalink sp_Print

Hi Mynda,

Thank you for the reply

1. Since I usually work with files averaging ~5 million rows, I have the Power Pivot enabled.

2. Earlier, even I thought to use a measure to populate values for best of network using max() function. However, I thought it wouldn't work because the columns HYD and 'Rest of Network' would change the figures for that measure.

3. 'Best of Network' figure was not necessary to be broken down month by month if it appeared as a column, but since you said that layout isn't possible, I would like to learn to break down the 'best of network' column by month as well.

 

I would again like to emphasize on point #2 - I am still in a state of confusion for adding 'Best of Network' as a measure owing to the already existing columns HYD and Rest of Network

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
May 20, 2020 - 8:06 pm
sp_Permalink sp_Print

Hi Salman,

Essentially what you want to do is treat this as another location called 'Best of Network'. To do that you'd have to add it to the rows of the table, which would then affect the Grand Total in your PivotTables. I don't think it makes sense to do this.

If you want it broken down by Month then at what point are you deciding which is the Best of Network value? e.g. on a month by month basis, or on a total for the period? If you choose on a month by month basis, then adding up those figures isn't going to give you an accurate total that represents one location because different locations could be the Best of Network each month.

You need to think this through more thoroughly.

In the attached file I've used Power Query to extract the Best of Network Product and Service values and added them as fields to your source data so they can be included in the PivotTable. I think this partially illustrates the issues with what you're wanting to achieve.

Mynda

Avatar
Salman Baig
Member
Members
Level 0
Forum Posts: 10
Member Since:
May 16, 2020
sp_UserOfflineSmall Offline
9
May 22, 2020 - 5:08 am
sp_Permalink sp_Print

Hi Mynda,

Thanks a ton for your support on this. 

It really helped me expand my horizons with respect to Power Query. Using the approach that you illustrated in the excel sheet (list.max), I was able to create month by month "Best of Network". Totally agree with you on the fact that adding up the figures won't give an accurate value, hence I'll be hiding the grand totals.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham, Riny van Eekelen
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27295

 

Member Stats:
Guest Posters: 49
Members: 31920
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.