Articles Blog

Power Query – Trim, whitespace and number conversions

Power Query – Trim, whitespace and number conversions


I’m Marcel Beugelsdijk
from The Netherlands owner of Bemint and
specialized in Power Query. This video is about the Trim function
in Excel and Power Query, and about whitespace and about a function you can use to convert numbers to hexadecimal or any other base and vice versa. In Excel, if you have a text like this
with leading and trailing spaces and multiple spaces in between, The Excel TRIM function would remove all spaces from a text string except for single spaces between words. So this would be the result from the Excel trim. If we import this table in Power Query and
we add a column … Format … Trim, then only the leading and trailing spaces are removed, and multiple spaces in between are kept. If we take a look at this Power Query Functions Dashboard, the Trim functions, there are 3 Trim functions in Power Query,
Trim, TrimEnd and TrimStart, and according to the function description, the Trim function removes
all leading and trailing whitespace. So, big question is: “What is a whitespace?” This is specified in the
Power Query M language specification. And in this document, section 2.4,
whitespace characters are defined as any characters with Unicode class Zs, and some other specific characters of which the hexadecimal code is provided in this section. I created a query, called “WhiteSpaces”. There’s a query summary you can read.
All the steps are explained over here. I created a table with in the first 2 columns
the hexadecimal codes of all whitespace characters and their description. The source of these codes are this document, so all the characters mentioned over here are included. And with regard to
the Unicode class Zs characters, I found a list over here. Back to this query. What I did is, I created a list with all the whitespace codes and their description in the Source step. Then I invoked custom function fnNumberBaseConversion to convert the hexadecimal code
into decimal codes. I will explain fnNumberBaseConversion later on. Next, I added a column with the character from the decimal code using Character.FromNumber. I inserted the text length so you can clearly see that the length of the text is 1. By the way, notice that in row 4 and 5, there are some carriage return and line feeds, so these lines are a bit larger than other lines. That is also determined by parameter
“Show whitespace” on the View tab. That’s both in Excel as
in Power BI Query Editor. If you uncheck the “Show whitespace”,
then everything is on 1 line, but if you have this checked, then you will also see carriage return and line feed characters. I inserted the trimmed text,
so I just added a column using Text.Trim And then, as a check again the text length, after the trim, to indicate that all the characters in this list
are actually trimmed, or removed. And likewise would happen with
Text.TrimEnd and Text.TrimStart. Also notice that there is a second parameter with all of these functions: a trim character, where you can provide another character
that can be trimmed from the text. So if you want to trim let’s say only a space, then you can provide a space over here, then only a space will be trimmed and then we will see all text lengths 1, except where there’s a space, because now I have specified
that spaces must be trimmed. So there is a big difference between the Power Query Trim functions and the Excel TRIM functions. Excel will only trim spaces. Also in the middle of the text. Power Query will remove – by default – all whitespace characters but only from the beginning and
the end of the text. Also function Splitter.SplitTextByWhitespace
will remove whitespaces. I created a query just to illustrate
that all whitespaces all groups of whitespaces, are considered as 1 whitespace. So I created a string starting with a capital “A” and ending with a capital “B” and in between are all the whitespace characters from table “Whitespaces”. So, this column,
with all the whitespace characters, And this string is splitted using splitter
Splitter.SplitTextByWhitespace. In the Power Query (M) Functions Dashboard,
you can search for all splitter functions, and the Splitter.SplitTextByWhitespace
is over here. Splitter.SplitTextByWhitespace returns a function that splits text into a list of text at whitespace So in this query, this part is the function. And this part is the parameter to the function. I can split this in 2 steps: I call this “Splitter”. and then add an additional step:=Splitter(Source) and then it is splitted. This function can be used to convert numbers from decimal to some other base. And vice versa. So for instance, if I have input number 12
and I want to convert it to hexadecimal, with an output length of 3, then I can invoke this function and I get zero zero C. The other way around is also possible: so if you would have zero zero C if the first parameter is a text,
it will be converted to a decimal number. and the length is not required then,
so you can leave it out. So “00C” hexadecimal is 16. It can also be used with base 32, I don’t know if this is valid,
I get some negative number, so I don’t think these are valid codes. And base 64 can also be used. And of course you can also convert to hexa…
to binary, for instance 1024 base 2 and you get this string. So far this video about Trim functions in Excel, Power Query, the whitespaces in Power Query and the function fnNumberBaseConversion. Thanks for watching and see you again
in another video about Power Query.

7 thoughts on “Power Query – Trim, whitespace and number conversions”

  1. Dear Sir, I need your help. I have a table which have a source of https://api.coinmarketcap.com/v1/ticker/. From this API I get the cryptocurrency information which is updated on hourly basis. I want to gather the same information in an automated way. Right now I make the duplicate table for each hour based on the source table, hence make the 5 duplicate tables for gathering the data. After that I run the append query to compile all the data in a single table and then I use the "Box and Whisker chart" for my desired result. Please guide me to make it automated.

  2. Hello I have a situation that i have a Number as a text in PQuery with a space 7 500,00, that i can not take that space. Thanks in advance

Leave a Reply

Your email address will not be published. Required fields are marked *