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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,722
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,722
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,101,754
Messages
5,482,680
Members
407,357
Latest member
Chazs

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top