String text parsing-where, why, and how

[NicLeam]

I'm very new to PowerBi, coming from a SAS background. Any help would be appreciated!

Firstly--I'm not sure what functions I should carry out in the Query Editor, and what I should try to do in the table/Home view. Is there a reference graphic/flowchart?  In the Query Editor, I have done some basic data manipulation (filtering, renaming, upcasing, and replacing text within rows (ReplaceValue)).

1. Is there a summary of the query step outcome--e.g., where PowerBi would tell me "X number of times 'OldValue' was replaced by 'NewValue'?" 

2. What's the best/quickest way to determine if a step 'worked'?

3. I have several large text fields. I want to create flags to determine which key terms occur.

For example: Variable/column name=Recommendation.  Ideally would like to create variables like Autopsy_flag, Record_flag, Hospital_flag. If the text 'AUTOPSY' occurs anywhere in the very long string Recommendation, would like Autopsy_flag=1. And so on for the remaining flags. Then I would like to use combinations of those flag results to filter (drop/keep) observations. 

To create these new variables: Do I need to do it in Query Editor? Or do I need to do calculate a measure (and if so--why and how)? I have tried various bits of DAX in places and nothing seems to work, though I seemed to create a new measure, it wasn't visible in the table, and it didn't appear to actually work (all zeros when I dragged that field into the Visualization table report canvas), though no error message was displayed.

I've tried things like:  

Custom Column: Autopsy_flag=IF(CONTAINS(#"Previous Query Step","Previous Query Step"[Recommendation], "AUTOPSY"),1,0)

Create New Measure: Autopsy_flag=IF(CONTAINS('Table Name','Table Name'[Recommendation],"AUTOPSY")1,0)

I would like to efficiently evaluate strings for terms and combinations of terms, try to identify misspellings, aberrant data, etc, so I'd really appreciate a detailed response on the best way to approach these types of tasks. Thank you!

[v-shex-msft · Accepted solution]

Hi @NicLeam.

>>1. Is there a summary of the query step outcome--e.g., where PowerBi would tell me "X number of times 'OldValue' was replaced by 'NewValue'?" 

Power bi only alert you that: current value will be replaced. Since these replacement operation not affect the original datasource, you not need to worry about this.(you can change the active steps to review the data in different steps). 

>>2. What's the best/quickest way to determine if a step 'worked'?

I'd like to suggest you refer to below link which about use multi conditions to replace value:

Multi Condition Logic In Power Query

>>3. I have several large text fields. I want to create flags to determine which key terms occur.

I think you can add new column and use text.contains function to check specific characters string.

Function Description

Text.Contains

Returns true if a text value substring was found within a text value string; otherwise, false.

>>Do I need to do it in Query Editor? 

For these modify operations such as 'replace', 'rename', 'filter', 'upper', I think power query will be suitable. Another calculations and check value opertions, I think dax will be suitbale.

Regards,

Xiaoxin Sheng

[NicLeam]

Thank you for your feedback! I will play around w/the CONTAINS function some more and see if I can get it to work.