Power Query: How Long Do We Have?
26 July 2017
Welcome to our Power Query blog. Today I look at how to replicate the Excel LEN() function.
In Excel(lent) Functions, I covered how to replicate some commonly used Excel functions. Readers will recall that although Power Query is an add-on for Excel, it does not always work in the same way. The M code behind Power Query uses different functions, which can be annoying when I am used to using familiar Excel functions. Today, I am going to look at another favourite Excel function, namely LEN():
LEN(text) returns the number of characters in a text string.
I have my data and suddenly I decide that I must know how many characters there are in the Expense Type column below:
I will use the Excel function first to show that my Power Query calculation will return the same value.
Now I have my column showing me what Excel calculates the text length to be, let’s try this in Power Query. In the ‘POWER QUERY’ tab, I choose the ‘From Table/Range’ option in the ‘Excel’ section and load my data into the Power Query Editor:
My next step is to find a text function that will do a similar job to LEN(). In the Power Query formula pages, I find the following:
The very first function appears to do exactly what I want –
Text.Length: Returns the number of characters in a text value
In the ‘Add Column’ tab, I choose to create a ‘Custom Column’:
Now when I click ‘OK’, I can compare my two columns:
My calculations match. This means that when I need to include a text length on a large data set, I can find it in Power Query which is quicker and more convenient if I plan to use it in further calculations in Power Query, or simply on large amounts of data.
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!