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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-34-image1.png/8d2d7010074d75669810afbae7f6a1d9.jpg)
I will use the Excel function first to show that my Power Query calculation will return the same value.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-34-image2.png/227143009536a8256784355989cefd3d.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-34-image3.png/e76fd7c4ddf6d86f74914dbe0da6a058.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-34-image4.png/2c990b7b894ebfb1fb2b8dfc136aa274.jpg)
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’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-34-image5.png/1a4ecafa8b84c7c1bc0b5699edc6b2f8.jpg)
Now when I click ‘OK’, I can compare my two columns:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-34-image6.png/1e6f1048ef7874085ffccdcf9d75e207.jpg)
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!