Forum Discussion
Custom Column
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.
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,
2 Replies
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,
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.
