Power Query: How Long Do We Have?

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():

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 –

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!