What s the best approach to handle text-based data in Power BI especially when applying search or pattern-matching transformations

0 votes
What’s the best approach to handle text-based data in Power BI, especially when applying search or pattern-matching transformations?

I’m working with a Power BI dataset that includes a lot of text-based data, and I need to perform transformations that involve search and pattern-matching within the text. I’m finding it challenging to optimize these text-based operations without impacting performance. What are the best practices for managing text data transformations in Power BI, especially for search and pattern matching?
Nov 12 in Power BI by Evanjalin
• 6,770 points
52 views

1 answer to this question.

0 votes

Dealing with text data in Power BI, especially in search and matching patterns, can be very performance-consuming and may prove challenging in carrying out the changes desired. A few of the recommended practices include the following.

Do Text Transformations Using Power Query: It would be best to use Power Query to transform and clean the text data before using it in your Power BI model. You can use Power Query’s functions like Text. Contains Text. Start, Text. End and Text.Middle to locate or find particular patterns in the Text. In these cases, the overhead of the Power BI model can be alleviated by utilizing custom functions and M code scripts from the Power Query editor for the most complex transformations. Also, when it comes to applying such transformations, it is advised that transformation is done in Power Query instead of DAX. This is due to the fact that DAX calculations are done on request and can greatly affect the responsiveness of reports due to the time taken to calculate for large datasets, especially for resources intensively focused areas, trends, and comparisons.

Do Not OverUse Calculated Columns: In cases where you have already loaded data but still need to transform Text, then calculated columns in DAX must be used in moderation. For example, when only a keyword needs to be found within a string or some characters need to be extracted from the beginning or end of a word, functions like SEARCH or LEFT/RIGHT in DAX are effective. It is, however, important to note that it is not advisable to apply these on large-scale data sets. This is because DAX transformations in calculated columns are memory-intensive and slow down refresh and load time due to dieting. More often than not, it is easier and faster to carry out these in Power Query and load the transformed data.

Enhance the Data Model of Power BI for Better Pattern Matching: In cases where more pattern matching is required, it is advisable to either index or code the Text in Power Query, which reduces the complex patterns present to simpler labels. For example, instead of performing a pattern-matching calculation to check which product name belongs to what category, one can create a table linking product categories to product category names and ensure that a relationship or a lookup connects it. In addition, the bulk of text data can be curbed by sensible extraction of non-essential columns or extraneous characters towards the early stages of this process. This enables Power BI to work better by reducing the amount of text data that it has to handle to a bare minimum.

To maximize text transformations within Power BI and enhance the overall experience, pre-processing in Power Query, reducing DAX calculations, and structuring the textual information are possible.

answered Nov 12 by pooja
• 6,530 points

Related Questions In Power BI

0 votes
0 answers

What’s the best approach to handle non-tabular data sources (e.g., unstructured data) in Power BI?

What’s the best approach to handle non-tabular ...READ MORE

Nov 18 in Power BI by Evanjalin
• 6,770 points
34 views
0 votes
0 answers
0 votes
1 answer

What is the best way to handle and display hierarchical data in Power BI?

Display of hierarchical data in Power BI ...READ MORE

answered Nov 5 in Power BI by pooja
• 6,530 points
53 views
0 votes
0 answers

What’s the best approach to handling slowly changing dimensions (SCD) in Power BI?

What’s the best approach to handling slowly ...READ MORE

Nov 7 in Power BI by Evanjalin
• 6,770 points
39 views
0 votes
1 answer

Displaying Table Schema using Power BI with Azure IoT Hub

Answering your first question, Event Hubs are ...READ MORE

answered Aug 1, 2018 in IoT (Internet of Things) by nirvana
• 3,130 points
1,338 views
+1 vote
1 answer

Unable to install connector for Power Bi and PostgreSQL

I think the problem is not at ...READ MORE

answered Aug 22, 2018 in Power BI by nirvana
• 3,130 points
2,738 views
+2 votes
2 answers

Migrate power bi collection to power bi embedded

I agree with Kalgi, this method is ...READ MORE

answered Oct 11, 2018 in Power BI by Hannah
• 18,520 points
1,509 views
+1 vote
1 answer

Connect power bi desktop to dataset and create custom reports

Yes using Power BI REST API to ...READ MORE

answered Sep 18, 2018 in Power BI by Kalgi
• 52,350 points
1,658 views
0 votes
1 answer
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP