Automatic format in column to remove all numbers and special characters

[GabrielRP]

I'm a beginner in BI, currently I'm doing a BI for a company that I work. We have 9 sales channel, and when we receive data from our ERP Api, the data comes with order number.

I needed to filter the sales channel to a standardized column to make some dashs with it, I've done some research and found a video teaching how to format a column to remove all numbers and special characters, doing that I created another column:

That way I made this dash with all sales channel and the sales total:

But now I'm having a problem, when we have a sale, the API gets the "observacaointerna" column with the sales channel and its order number and doesn't update the dash and I need to format and remove all numbers and special characters every day. So I'd like to know if there's a way to automatically format the data and remove all numbers and special characters.

[JorgePinho]

Hey. Can you tell me how you removed all numbers and special characters? Was it in Power Query?

[GabrielRP]

Jorge, I've removed in Power Query, I made another column and used the function: Text.Select([column],{"A".."z"}). The video that helped me: https://www.youtube.com/watch?v=II4wvIifIQg

[JorgePinho · Accepted solution]

If you used Power Query, each time Power BI refreshes data it will apply the transformation steps you defined automatically to the new data so I can't understand the problem. How are you refreshing the data?

[GabrielRP]

Jorge, I didn't know this. I just see that our data didn't update, that's why the sales number from channels haven't increased. To know that Power BI refreshes data with transformation steps is awesome. Thank you so much.

[JorgePinho]

You're welcome. Check this training to know more about this: https://docs.microsoft.com/en-us/training/modules/clean-data-power-bi/