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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,833
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,833
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,641
Messages
5,488,052
Members
407,620
Latest member
abhilenka

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top