If statement to convert text to value - Power Query

bluelabel

Board Regular
Joined
Nov 27, 2008
Messages
76
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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
maybe try to add custom column: Text.Trim([raw],{"A".."Z","a".."z"})
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top