How to convert text to number - numbers are downloaded from external software as text

[PQ]

How to change the text to a number when is downloaded from other software as a text? When the column is converted from text to number it shows an 'Error' (using PowerQuery). I need to see the value in the whole column/columns for my further calculations. I have 100k lines and several columns to work with each month. Please can anybody help, preferably with a snapshot? 

[HotChilli]

Please show the format of the numbers you have and examples of values that don't convert properly.

[PQ]

Hi, I managed to sort that out, thank you. However I am having a bigger issue. I cannot find how to create VLOOKUP similar to Excel, to find a value in a column -> replace it with a value from a different column (the some table) as per the condition value found in the other column. To descripe:

On my monthly update with thousands of lines from the raw data list I need to replace in Power Query [External Tx ID] "rexxxxxx" (based on "refund" in [Tx Type] column) with "pixxxxxx" (based on original "purchase" in [Tx Type] column), which match as vaule "Uxxxx" (in [ID] column.

As a result I need to Add Column with "pixxxxx" value on both lines, means of "purchase" and "refund" line, to be able to further work with it. Please could you help?
Please see more on the snapshot please to better understand my notes above.

[ronrsnfld]

Your question is confusing. You write you "need to Add Column with "pixxxxx" value on both lines" but you don't show an added column. You also write about doing a "Replace" which is what you show. What do you really want?

[PQ]

My apologies for not being clear. I spent a lot of time on it yesterday without success.

I need to see in a new created column (list, not created yet) only "pixxxxx" values (this means purchases values).

On this raw file there is "pixxxxx" value on purchase and "rexxxxx" value on return.

To be able to work with this file I need to see only "pixxxxx" values in the column.

That means where is "rexxxxx" on the line for return, I need to find line wiht "pixxxx" (purchase), for the right purchase, and replace that "rexxxx" value with original "pixxxxx" value.

This is because the above file is link to other report, Stripe paymet. There is pay and re-pay alway under the some "pixxxx" value.

Is that more clear now, please?

[ronrsnfld · Accepted solution]

If I understand you correctly, here's one way:

Add a custom column from the GUI and name it whatever you want.

Formula:

if [Tx Type] <> "refund" then [External Tx ID] else 
Table.SelectRows(#"Changed Type",
    (t)=>t[ID]=[ID] and 
         t[Tx Type] = "purchase")[External Tx ID]{0}

Before:

After: