The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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"})
Solved! Go to 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
can you upload some sample data?
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
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 Column
Error Table.TransformColumn
How Play to get it to work?
What if you just use ( or work into to your code) the Split Column-->By Nondigit to digit?
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
= 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"