Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Need help with grouping by filtering contains

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

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

transform your data this way...

20200114_TextForDelimiter.png

20200114_TextForDelimiter_2.png

20200114_TextForDelimiter_3.png

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


View solution in original post

6 REPLIES 6
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

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

1.PNG  

 

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

2.PNG 

 

The final result will be like below:

3.PNG 

 

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

Thanks alot this helps!

amitchandak
Super User
Super User

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

Thanks Amit for your help, appreciate it!

Hi @Anonymous ,

 

transform your data this way...

20200114_TextForDelimiter.png

20200114_TextForDelimiter_2.png

20200114_TextForDelimiter_3.png

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


Anonymous
Not applicable

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

Appreciate your effrots and time!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.