Custom Column

[bpierceaxo]

I am trying to create a Custom Column with data from another column.   I have a column that has 4 digits for the Dept # and I only want to use the first three.   When I use the Left function -

= Table.AddColumn(#"Sorted Rows", "Dept", each LEFT([code], 3))      code is the orginal column name

I get the following error message.

Expression.Error: The name 'LEFT' wasn't recognized. Make sure it's spelled correctly.

[DataNinja777 · Accepted solution]

Hi @bpierceaxo ,

The error occurs because the LEFT function is used in Excel and DAX, but not in the Power Query M language. The correct Power Query function to extract the first characters from a text string is Text.Start.

You can fix the issue by updating your formula to use the correct function. The new formula will get the first three characters from your [code] column and place them in your new Dept column.

= Table.AddColumn(#"Sorted Rows", "Dept", each Text.Start(Text.From([code]), 3))

In this corrected formula, Text.Start replaces LEFT. It's also a good practice to wrap your column name in Text.From(), as shown. This step ensures Power Query treats the column's data as text, which prevents errors if the original column has a number format.

Best regards,

[mdaatifraza5556]

Hi @bpierceaxo 

Can you please try the below M code ?

= Table.AddColumn(#"Sorted Rows", "Dept", each Text.Start([code], 3), type text)

If this answers your questions, kindly accept it as a solution and give kudos.