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.
