Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Transform Column LEFT([column], n) Using M Syntax Without Add New Column

I would like to use the equivalent of an LEFT([column], n) function using M syntax within Edit Query to Transform Column without having to create a new column in order to accomplish this. The syntax I have bee trying ERRORS and I do not find examples using M syntax for this, seemingly routine and simple function. Please help. Here are examples of what I have tried and do not work.

= Table.TransformColumns(#"Renamed Columns", "Zip", each Text.Start([Zip], 5))
= Table.TransformColumns(#"Renamed Columns",{{"Zip", each Text.Start([Zip], 5), type text}})
Expression.Error: We cannot apply field access to the type Text.
Details: Value="30308" Key=Zip

= Table.SelectRows(#"Renamed Columns", each Text.Start([Zip], 5))

= Table.TransformColumns(#"Added Column 5 Character Zip",{{"Mail Zip", Text.Length, Int64.Type}}) ... this does not throw an errow but only returns the length of the text ... i.e., transforms the column by replacing the text with the length value

BTW ... The Syntax Below Works but it seems weird to have to "SPLIT" the column instead of using a LEFT type function
= Table.SplitColumn(#"Changed Type", "Event", Splitter.SplitTextByPositions({0, 3}, false), {"Event"})

1 ACCEPTED SOLUTION

I feel your pain.  The documentation is certainly lacking.

I played about and got this to work.

 

Table.TransformColumns(#"Trimmed Text",{{"your column here", each Text.Start(_, 2), type text}})

I would also welcome any contribution from Nick

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

can you upload some sample data?

Anonymous
Not applicable

Column1,Column2
NIN0001,This
NIN0002,That
NIN0003,IsAt
INI0001,This
INI0002,That
INI0003,IsAt

 

Transform Column1:
Column1,Column2
NIN,This
NIN,That
NIN,IsAt
INI,This
INI,That
INI,IsAt

I feel your pain.  The documentation is certainly lacking.

I played about and got this to work.

 

Table.TransformColumns(#"Trimmed Text",{{"your column here", each Text.Start(_, 2), type text}})

I would also welcome any contribution from Nick

Anonymous
Not applicable

Not sure what you mean by "I played about and got this to work".  I added a new step in the appropriate place and pasted your syntax into that new step and the syntax did not compile ... did not work.  

Syntax Transform ColumnSyntax Transform Column

Error Table.TransformColumnError Table.TransformColumn

How Play to get it to work?

Anonymous
Not applicable

What if you just use ( or work into to your code) the Split Column-->By Nondigit to digit?

Split NonDigit to Digit.png

Anonymous
Not applicable

Split will work ... but why split and then have to delete the extra column ... should be a LEFT([column],value) type function ... this is basic and frequently used type of functionality

"I played about and got this to work" - just an expression.  I experimented with some data and got the desired result.

It does work.

 

Post some data and the contents of your Advanced Editor script and I'll have a look

Anonymous
Not applicable

= Table.TransformColumns(#"Changed Type",{{"Event", each Text.Start(_, 3), type text}})

Worked ... may have had a typo in my previous attempt.

Thanks.  I will mark your previous as "Solution"

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors