If statement to convert text to value - Power Query

bluelabel

Board Regular
Joined
Nov 27, 2008
Messages
73
Hi,

I am having trouble converting an excel formula into a power query statement.

The formula is =VALUE(IF(ISNUMBER(VALUE(MID([DocNum],6,1))),[DocNum],LEFT([DocNum],5)))

I have it in a custom column, but would like it to ideally be used as a formula in the [DocNum] column in power query if that is possible.

The issue I have is that there a few entries in the source data where there is a document number which will have a letter suffix. (123A, 234A, etc) when the source data is formatted as a text, any entry which has the letter suffix imports as an error and a null cell in power query.

How do I get around this where I can simply drop the suffix letter and import the numbers before that.

Thanks!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,782
maybe try to add custom column: Text.Trim([raw],{"A".."Z","a".."z"})
 

bluelabel

Board Regular
Joined
Nov 27, 2008
Messages
73
maybe try to add custom column: Text.Trim([raw],{"A".."Z","a".."z"})
Thank you for replying.

It is throwing an error and I think that might be because 99.9% of the data in that column is in fact a number, but the data i need this to work on is stored as text.

The raw data is in an excel sheet if that helps.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,782
did you change data type to text in source column?

adapt to your needs:

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeText = Table.TransformColumnTypes(Source,{{"raw", type text}}),
    Numbers = Table.AddColumn(TypeText, "Numbers", each Number.FromText(Text.Trim([raw],{"A".."Z","a".."z"})))
in
    Numbers[/SIZE]
rawNumbers
123A
123​
234B
234​
345​
345​
AB567CD
567​
 
Last edited:

bluelabel

Board Regular
Joined
Nov 27, 2008
Messages
73
did you change data type to text in source column?

adapt to your needs:

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeText = Table.TransformColumnTypes(Source,{{"raw", type text}}),
    Numbers = Table.AddColumn(TypeText, "Numbers", each Number.FromText(Text.Trim([raw],{"A".."Z","a".."z"})))
in
    Numbers[/SIZE]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]raw[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Numbers[/COLOR]
123A
123​
234B
234​
345​
345​
AB567CD
567​

<tbody>
</tbody>

I had to customise this for my needs, but it worked perfectly. Thanks so much.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,378
Messages
5,486,518
Members
407,550
Latest member
LucasBordure

This Week's Hot Topics

Top