Combine similar column values and average them

[aditamir]

Hi,
in my datasheet, I have similar data which is repeated in different ways, and I want to combine them under one title and perform an average calculation of their values. for example, under the column "JOB_TITLE" several options for an assistant. I want to combine all of them to under only "assistant" and for column "YEAR_SALARY" I want to average all the values.

(There is a problem with placing the full image here, so here's the URL)
https://i.ibb.co/LY6s1Hb/2021-05-31-19-54-49.jpg

Thanks for all the helpers (:

[selimovd · Accepted solution]

Hey @aditamir ,

I would group them in Power Query. Add a new column and check for certain criteria.

You could check if the name starts with "ASSISTANT" with the Text.StartsWith function.

For example:

Text.StartsWith([Job_Title], "ASSISTANT")

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic