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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply

how to create a mechanism in power bi that filters bad comments in a table?

I have a table with questions and answers, a kind of evaluation of customers' reactions to the store, is it possible to create some mechanism in Power BI where I can filter bad comments/evaluations? All text is free and entered manually

2 ACCEPTED SOLUTIONS
123abc
Community Champion
Community Champion

Yes, you can create a mechanism in Power BI to filter out bad comments or evaluations from your table containing customer reactions. Here's a general approach you can take:

  1. Define Criteria for Bad Comments: Determine what constitutes a "bad" comment or evaluation. This could include specific keywords, sentiment analysis, or a combination of factors like low ratings or negative phrases.

  2. Create a Filter Column: Add a new column to your table that flags each comment as either "Bad" or "Good" based on your defined criteria.

  3. Use Text Analysis Techniques: Power BI offers several text analysis techniques that can help identify sentiment or key phrases within text data. You can use functions like CONTAINSSTRING, FIND, or SEARCH to identify specific keywords associated with negative comments.

  4. Apply Conditional Formatting: Once you've identified bad comments using your defined criteria, you can apply conditional formatting to visually distinguish them in your table or report.

  5. Utilize Advanced Analytics Functions: Power BI provides various advanced analytics functions that can assist in analyzing text data, such as sentiment analysis through the use of Azure Cognitive Services or custom Power Query M code.

  6. Implement User-Defined Functions (UDFs): If the built-in functions in Power BI are not sufficient for your needs, you can create custom functions using Power Query M language or incorporate external APIs for more advanced text analysis.

  7. Continuously Refine and Test: It's important to continuously refine your criteria and mechanisms for identifying bad comments based on feedback and analysis. Regularly testing and adjusting your approach will help improve the accuracy of your filtering mechanism over time.

By following these steps and leveraging the capabilities of Power BI, you can create an effective mechanism for filtering out bad comments or evaluations from your table of customer reactions.

 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

View solution in original post

v-huijiey-msft
Community Support
Community Support

Hi @jeanbinhozouza ,

 

123abc said it well.

 

A simple example is below, please follow these steps:

 

1. This is the original data I created.

vhuijieymsft_0-1709017084931.png

 

2. The rule I set is that if the comment contains the keywords "bad", "poor", "terrible", and "disappointed", it will be classified as "Negative", otherwise it will be classified as "Positive".

 

In Power Query , select "Add Column" > "Custom Column".

 

In the Custom Column formula, use the following M code, adjusting the keywords as necessary:

= if Text.Contains([Comment], "bad") or 
 Text.Contains([Comment], "poor") or 
 Text.Contains([Comment], "terrible") or 
 Text.Contains([Comment], "disappointed") 
 then "Negative" else "Positive"

vhuijieymsft_1-1709017131702.png

 

3. In your report, add a visual that includes the comments.

 

Drag the "Sentiment" column to the Filters pane.

 

Choose to show items when the value is "Positive" to exclude negative comments, or "Negative" to focus on them.

 

4. You can also set up conditional formatting to distinguish between different categories of data.

vhuijieymsft_2-1709017131704.png

vhuijieymsft_2-1709017294245.png

vhuijieymsft_3-1709017306976.png

 

There are limitations to this, as the keywords of the comment may be increased.

 

Alternatives are:

 

1. Analyze the sentiment of each review using Azure Cognitive Services.

 

2. Add the sentiment results to the dataset, possibly in a new column called "Sentiment".

 

3. In Power BI, use the Filter pane to exclude reviews with "negative" sentiment.

 

For more information about Azure Cognitive Services text analytics, see:

What is sentiment analysis and opinion mining in the Language service? - Azure AI services | Microso...

 

If you have any further questions please feel free to contact me.

 

pbix file is attached.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

2 REPLIES 2
v-huijiey-msft
Community Support
Community Support

Hi @jeanbinhozouza ,

 

123abc said it well.

 

A simple example is below, please follow these steps:

 

1. This is the original data I created.

vhuijieymsft_0-1709017084931.png

 

2. The rule I set is that if the comment contains the keywords "bad", "poor", "terrible", and "disappointed", it will be classified as "Negative", otherwise it will be classified as "Positive".

 

In Power Query , select "Add Column" > "Custom Column".

 

In the Custom Column formula, use the following M code, adjusting the keywords as necessary:

= if Text.Contains([Comment], "bad") or 
 Text.Contains([Comment], "poor") or 
 Text.Contains([Comment], "terrible") or 
 Text.Contains([Comment], "disappointed") 
 then "Negative" else "Positive"

vhuijieymsft_1-1709017131702.png

 

3. In your report, add a visual that includes the comments.

 

Drag the "Sentiment" column to the Filters pane.

 

Choose to show items when the value is "Positive" to exclude negative comments, or "Negative" to focus on them.

 

4. You can also set up conditional formatting to distinguish between different categories of data.

vhuijieymsft_2-1709017131704.png

vhuijieymsft_2-1709017294245.png

vhuijieymsft_3-1709017306976.png

 

There are limitations to this, as the keywords of the comment may be increased.

 

Alternatives are:

 

1. Analyze the sentiment of each review using Azure Cognitive Services.

 

2. Add the sentiment results to the dataset, possibly in a new column called "Sentiment".

 

3. In Power BI, use the Filter pane to exclude reviews with "negative" sentiment.

 

For more information about Azure Cognitive Services text analytics, see:

What is sentiment analysis and opinion mining in the Language service? - Azure AI services | Microso...

 

If you have any further questions please feel free to contact me.

 

pbix file is attached.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

123abc
Community Champion
Community Champion

Yes, you can create a mechanism in Power BI to filter out bad comments or evaluations from your table containing customer reactions. Here's a general approach you can take:

  1. Define Criteria for Bad Comments: Determine what constitutes a "bad" comment or evaluation. This could include specific keywords, sentiment analysis, or a combination of factors like low ratings or negative phrases.

  2. Create a Filter Column: Add a new column to your table that flags each comment as either "Bad" or "Good" based on your defined criteria.

  3. Use Text Analysis Techniques: Power BI offers several text analysis techniques that can help identify sentiment or key phrases within text data. You can use functions like CONTAINSSTRING, FIND, or SEARCH to identify specific keywords associated with negative comments.

  4. Apply Conditional Formatting: Once you've identified bad comments using your defined criteria, you can apply conditional formatting to visually distinguish them in your table or report.

  5. Utilize Advanced Analytics Functions: Power BI provides various advanced analytics functions that can assist in analyzing text data, such as sentiment analysis through the use of Azure Cognitive Services or custom Power Query M code.

  6. Implement User-Defined Functions (UDFs): If the built-in functions in Power BI are not sufficient for your needs, you can create custom functions using Power Query M language or incorporate external APIs for more advanced text analysis.

  7. Continuously Refine and Test: It's important to continuously refine your criteria and mechanisms for identifying bad comments based on feedback and analysis. Regularly testing and adjusting your approach will help improve the accuracy of your filtering mechanism over time.

By following these steps and leveraging the capabilities of Power BI, you can create an effective mechanism for filtering out bad comments or evaluations from your table of customer reactions.

 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors