If statement to convert text to value - Power Query

[bluelabel]

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!

 

[sandy666]

maybe try to add custom column: Text.Trim([raw],{"A".."Z","a".."z"})

 

[bluelabel]

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]

did you change data type to text in source column?

adapt to your needs:

[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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][td][/td][td=bgcolor:#70AD47]Numbers[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]123A[/td][td][/td][td=bgcolor:#E2EFDA]

123​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]234B[/td][td][/td][td]

234​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

345​

[/td][td][/td][td=bgcolor:#E2EFDA]

345​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AB567CD[/td][td][/td][td]

567​

[/td][/tr]
[/table]

 

[bluelabel]

did you change data type to text in source column?

adapt to your needs:

[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]

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]raw[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Numbers[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]123A[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]

123​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]234B[/TD]
[TD][/TD]
[TD]

234​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]

345​

[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]

345​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]AB567CD[/TD]
[TD][/TD]
[TD]

567​

[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[sandy666]

I had to customise this for my needs

That's what I said

it worked perfectly.

Glad to help

Thanks so much.

You are welcome
Have a nice day