Register
Log In
Unanswered
Home
Excel Analysis Toolpak
October 17, 2015
Offline
Dear Fellow PQ Enthusiasts,
I need your help. I have a tight deadline on big report and this is where I'm stuck.
I have a file with Policies, Codes and Premiums - the INPUT in the attached file. Policies can have multiple codes.
I need to convert this data so each policy is on its own line, has just one code associated with it (the code with the highest premium), but the premium needs to be total premium for given policy - OUTPUT in the attached file.
Is there a way to do it with Power Query?
If not, what would be the best way to proceed?
Many thanks,
Blanka
July 16, 2010
Offline
Hi Blanka,
In the file attached you'll see a Power Query solution, however it relies on the Premium values being unique, which I doubt they will be in your real data set.
I've also inserted two PivotTables which you could use and just hide the columns you don't want to see.
Lastly, you could use formulas for this but that would be the least desireable and would also require the premium values to be unique.
Someone else might have a better/more robust solution for you.
Mynda
Excel Analysis Toolpak
October 17, 2015
Offline
Hi Mynda,
Thank you so much for your help.
You are right, Premium values are not always unique in my real data.
I ended up doing a combination of Excel formulas and Power Query. Not ideal, but this is a "once-in-a-while" report involving a large data set and I don't have to worry about repeating a lot of manual work time after time. I finally got it done.
Thanks again!
Blanka
Most Users Ever Online: 57
Currently Online: Meyanui
16 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Akshatraj Adig
Liesie Marais
Moet Lewis
Michelle Anderson
Veronica Gallego
krishnaraj r
Dharan Prakash Mishra
Md. Rahman
Lilian Vo
Jane Kumwenda
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
