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!
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
maybe try to add custom column: Text.Trim([raw],{"A".."Z","a".."z"})
 

bluelabel

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
76
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,127,762
Messages
5,626,720
Members
416,200
Latest member
Pulsar3000

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
Top