Wrangling Joined Salesforce Reports with Pivot Tables

[ExcelCT]

Hello all.

If anyone has used joined reports in Salesforce, you'll know the export output is limited to grouped, pre-formatted Excel sheets that are not (in Salesforce parlance) 'detailed reports'. For using pivot tables with these exports, this is a bit of a problem because there are not repeating rows and rather the data looks like below. The actual report is way more complicated, but I've made a sample of how the data looks. If with your help I can figure out how to get a working pivot table with this sample data, I'm confident I could handle the rest of the work.

About the data: This is the format it comes out of Salesforce. I can image some ways I could get my required output (see below) without using pivot tables - but I love pivot tables and would like to use them if possible. I know I could do some amount of "post processing" via macros or helper columns - and I may have to do that. I would like to keep post-processing to a minimum.



It's important to note that the strategy entries (or lack thereof) and the goals (or lack thereof) are independent of each other. That is the 'join' in Salesforce (in my example, the join is on Company). Thus each company can have 0 to n number of Strategies and 0 to n number of Goals. They have both. They may have neither. Obviously pivot table out of the box can't really deal with this format very well. As humans, it is easy to understand what I'm trying to get to, but not sure how to make a pivot table do this. I do have PowerPivot and have used it somewhat in the past but not an expert user of it. But if there is a PowerPivot-based solution, I could enact that.

Thanks in advance for your help.

-CT

 

[ExcelCT]

Hello all.

If anyone has used joined reports in Salesforce, you'll know the export output is limited to grouped, pre-formatted Excel sheets that are not (in Salesforce parlance) 'detailed reports'. For using pivot tables with these exports, this is a bit of a problem because there are not repeating rows and rather the data looks like below. The actual report is way more complicated, but I've made a sample of how the data looks. If with your help I can figure out how to get a working pivot table with this sample data, I'm confident I could handle the rest of the work.

About the data: This is the format it comes out of Salesforce. I can image some ways I could get my required output (see below) without using pivot tables - but I love pivot tables and would like to use them if possible. I know I could do some amount of "post processing" via macros or helper columns - and I may have to do that. I would like to keep post-processing to a minimum.



It's important to note that the strategy entries (or lack thereof) and the goals (or lack thereof) are independent of each other. That is the 'join' in Salesforce (in my example, the join is on Company). Thus each company can have 0 to n number of Strategies and 0 to n number of Goals. They have both. They may have neither. Obviously pivot table out of the box can't really deal with this format very well. As humans, it is easy to understand what I'm trying to get to, but not sure how to make a pivot table do this. I do have PowerPivot and have used it somewhat in the past but not an expert user of it. But if there is a PowerPivot-based solution, I could enact that.

Thanks in advance for your help.

-CT

View attachment 6615
View attachment 6616

Sorry the 'What I would like' had a (hand typed) typo in it for Microsoft counts. Here is the corrected version

 

[sandy666]

with Power Query (Get&Transform)

Company	Strategy	Goal		Company	Goal	Strategy
IBM		Invent PC		Apple	0	3
		Deep Blue		Facebook	3	1
		Oxygen Batteries		IBM	4	0
		Kinetic Energy		Microsoft	3	3
Microsoft	Software	MS-DOS
	Hardware	Zune
	Services	Azure
Facebook	Software	Facebook.com
		Instagram
		WhatsApp
Apple	Software
	Hardware
	Services

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillD = Table.FillDown(Source,{"Company"}),
    Unpivot = Table.UnpivotOtherColumns(FillD, {"Company"}, "Attribute", "Value"),
    Pivot = Table.Pivot(Unpivot, List.Distinct(Unpivot[Attribute]), "Attribute", "Value", List.Count)
in
    Pivot

 

[ExcelCT]

with Power Query (Get&Transform)

Company	Strategy	Goal		Company	Goal	Strategy
IBM		Invent PC		Apple	0	3
		Deep Blue		Facebook	3	1
		Oxygen Batteries		IBM	4	0
		Kinetic Energy		Microsoft	3	3
Microsoft	Software	MS-DOS
	Hardware	Zune
	Services	Azure
Facebook	Software	Facebook.com
		Instagram
		WhatsApp
Apple	Software
	Hardware
	Services

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillD = Table.FillDown(Source,{"Company"}),
    Unpivot = Table.UnpivotOtherColumns(FillD, {"Company"}, "Attribute", "Value"),
    Pivot = Table.Pivot(Unpivot, List.Distinct(Unpivot[Attribute]), "Attribute", "Value", List.Count)
in
    Pivot

That looks awesome! I am not sure if I have "Power Query/Get & Transform". For add-ons, I have Analysis ToolPak, Inquire, Power Map, Power Pivot, Power View, Solver, and Actions Pane 3. I work at a company that keeps all the PCs pretty "locked down" - so if don't already have it installed, I may or may not be able to get it installed . But will look into that. Thanks!

 

[sandy666]

2016 & 365 has Power Query and Power Pivot built-in
2013 need Power Query add-in

excelct.xlsx

Microsoft Excel Workbook

1drv.ms

 

[ExcelCT]

2016 & 365 has Power Query and Power Pivot built-in
2013 need Power Query add-in

Thanks! I just figured that out myself after some Googling.

Never used this before but I'll work to figure it out. Thanks so much for the script!

 

[sandy666]

refresh thread and see post#5 again

 

[ExcelCT]

refresh thread and see post#5 again

Thanks. I'll try to unpack and understand what's going on. My real sheet is more complicated but if I can reverse engineer what's happening in your script, I should be able to get it to work for my real-life worksheet.

Thanks again.

 

[sandy666]

1. select your range (first picture)
2a. Ctrl+T (it will create an Excel Table, check the name of this table in Name Manager, should be the same as in the M-code)
or
2b. use Data - From Table - it will open Power Query Editor

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[ExcelCT]

1. select your range (first picture)
2a. Ctrl+T (it will create an Excel Table, check the name of this table in Name Manager, should be the same as in the M-code)
or
2b. use Data - From Table - it will open Power Query Editor

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

I had tried to create a named range called Table1 but that didn't work. Your method worked fine, however.

Thank you SO much. Looks like it is working now.