This is a quick method about extracting only letters from a string. It is part of the Week2 “Preppin’ data” challenge.
Task for extracting letters from a string
Image you have a string like so: “10.ROADBIKES.423/01” and would like to extract only “ROADBIKES”.
Power Query actually has a function for this purpose: Text.Select. It takes 2 parameters:
- The text to select from
- A list of characters that shall be selected
For the given example the code would look like so:
Text.Select( "10.ROADBIKES.423/01", {"A".."Z"})
This function is always case sensitive as there is no optional parameter that accepts a comparer function.
Easy application
Although the function is not available through the UI, it can nonetheless easily be applicated. Just use a different dummy-text transformation function and then edit the code afterwards. That way you only have to type in a tiny fraction of the code:
Tweak to Text.Select for extracting letters from a string
Please check the video if you want to see how to use this function without having to manually code it in the advanced editor. You’ll also learn how to apply aggregations on groupings easily:
Enjoy and stay queryious đ
The original data type of Order Date and Shipping Date in the source file “PD 2021 Wk 2 Input – Bike Model Sales.csv” are text. I tried to change them to date but found a lot of errors like:
DataFormat.Error: We couldn’t parse the input provided as a Date value.
Details:
15/05/2020
What’s wrong and how can I fix it? Please advise.
You have to use a locale that has the month on the 2nd position. You can either add a it as a parameter manually, or check how to do it via the UI here: https://www.oraylis.de/blog/local-date-formats-in-power-bi#:~:text=In%20the%20query%20editor%20you,select%20Change%20Type%2FUsing%20Locale.&text=Now%20you%20can%20choose%20the,the%20Locale%20and%20that%27s%20it.
UK date format should work: = Table.TransformColumnTypes(#”Promoted Headers”, {{“Shipping Date”, type date}}, “en-GB”)
Many thanks to you
wunderbare Video. liebe das. danke!
How would you keep only vowels, ie. A, E, I, O and U. I have tried various Text.Select and cannot find a solution.
Please try Text.Select( “10.ROADBIKES.423/01”, {“A”, “E”, “I”, “O”, “U”})
@Imke,
That is the exact expression I have attempted and PQ errors on that statement. It shows the error as ocuring after the comma after the letter A.