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
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
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
