Need help with grouping by filtering contains

[Anonymous]

I am fairly new to Power BI, I have imported some data to power BI and I am able to get data in tabular format like below (Current Output). I want to filer/ group data and generate a report that gives the details as shared below in required output.

Current Output

Name                     Cost

Entity1_Var1            10

Entity1_Var2            15

Entity1_Var3            5

Entity2_Var4            20

Entity2_Var5            15

Required Output

Name                     Cost

Entity1                    30

Entity2                    35

[v-yuta-msft]

@Anonymous ,

Clicl Query editor-> Transform-> Split Columns as below:

  

After remove the useless column, click Transform-> Group by as below:

 

The final result will be like below:

 

You can refer to the pbix file.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks alot this helps!

[amitchandak]

You can create new column

new Col =left(table[Name],(len(table[Name])-search(table[Name],"_")))

In table or matrix, now you can use new table and sum Agg with cost.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Thanks Amit for your help, appreciate it!

[mwegener · Accepted solution]

Hi @Anonymous ,

transform your data this way...

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Anonymous]

Thank you Marcus, this was exactly what I was looking for.

Appreciate your effrots and time!