Extract text from one column into another

[thorrrr]

HI Guys

Got an extract from Paypal ready to import into Finance Software see image below I need to move the negative number from inflow to outflow can it be done. Please note I am semi-newbie to this so may need a little more explanation if that it possible.

Thorrrr

[Anonymous]

Try adding this step to your Power Query, assuming this condition only applies even if Outflow is not 0:

= Table.ReplaceValue(#"YOUR LAST STEP", each _, each if _[Inflow]<0 then _[Inflow] else _[Outflow],Replacer.ReplaceValue,{"Outflow"})

[thorrrr]

HI Steve

As I said I was very new to this how do I add a step?

I know there is an Advanced Editor but when I open it I would not know where to put it ?

Or do i add a new column ?

let
Source = Csv.Document(File.Contents("C:\Users\Dale\Desktop\PaypallALL.CSV"),[Delimiter=",", Columns=38, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Time", type time}, {"Time zone", type text}, {"Name", type text}, {"Type", type text}, {"Status", type text}, {"Currency", type text}, {"Gross", type number}, {"Fee", type number}, {"Net", type number}, {"From Email Address", type text}, {"To Email Address", type text}, {"Transaction ID", type text}, {"Delivery Address", type text}, {"Address Status", type text}, {"Item Title", type text}, {"Item ID", type text}, {"Postage and Packaging Amount", Int64.Type}, {"Insurance Amount", Int64.Type}, {"VAT", Int64.Type}, {"Option 1 Name", type text}, {"Option 1 Value", type text}, {"Option 2 Name", type text}, {"Option 2 Value", type text}, {"Reference Txn ID", type text}, {"Invoice Number", Int64.Type}, {"Custom Number", type text}, {"Receipt ID", type text}, {"Balance", type number}, {"Address Line 1", type text}, {"Address Line 2/District/Neighbourhood", type text}, {"Town/City", type text}, {"County", type text}, {"Postcode", type text}, {"Country", type text}, {"Contact Phone Number", type text}, {"Subject", type text}, {"Note", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Date", "Name", "Type", "Gross", "Fee"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Name", "Payee"}, {"Type", "Memo"}, {"Gross", "Inflow"}, {"Fee", "Outflow"}})
in
#"Renamed Columns"

[Anonymous]

To add the required step, try replacing your last THREE lines with the lines in red below, in Advanced Editor (you need that comma at the end of all queries, except the last before the IN...:

#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Name", "Payee"}, {"Type", "Memo"}, {"Gross", "Inflow"}, {"Fee", "Outflow"}}),
#"Replaced Outflow With Negative Inflow" = Table.ReplaceValue(#"Renamed Columns", each _, each if _[Inflow]<0 then _[Inflow] else _[Outflow],Replacer.ReplaceValue,{"Outflow"})
in
#"Replaced Outflow With Negative Inflow"

[thorrrr]

I also note a syntax error line 2 with your code being reported by the Editor you have a " end of the line

I removed that then i had no Sytax errors reported  but is still errors

[Anonymous]

@thorrrr,

Sorry, that extra " was my fault, and I did edit the script to have a , at the end of your #"Renamed Columns" line but you may have beat me to it.

Please try with the corrected code above again.

[thorrrr]

Still get same error 

[Anonymous]

@thorrrr,

Sorry, I can't read that error message and it's hard to reproduce for you without sample data or the PBIX.  

Overnight though, I thought a simpler approach for you may in fact be to add a new Conditional Column (under the Add Column menu) per below screenshot, then work with that NewOutflow column as needed:

Add Conditional Column

[thorrrr]

HI

That only partially works because it is still leaving the neg numbers in Inflow. ON a small set it is fine to manually clean but if you have 1000's it would be a pain

[Anonymous]

So what value do you want in Inflow if you move it when negative to Outflow - e.g. does Inflow become zero in that case?

[thorrrr]

HI Steve

Inflow is money received from customers or refunds from Paypal you can see from the data not sure why Paypal do it but some payments out are listed as Inflow but obviously money going out is Outflow.

So Inflow should be positive numbers

Outflow Negative numbers  

[Anonymous · Accepted solution]

@thorrrr,

I think I understand what you need now.  Try the following code, which I recreated manually from yours so MIGHT still be a little off somewhere.  It now adds Inflow and Outflow as conditional columns based on negative Inflows etc., then deletes the original Gross and Fee columns at the end.  To check if it's working as you expect, click on Renamed Columns to the right under 'APPLIED STEPS' to see Gross and Fee, as well as the Inflow and Outflow columns to they are transformed.  For checking purposes, it may be worth deleting that last 'Removed Columns' step to retain Gross and Fee columns anyway.

If this doesn't meet your needs, please post a PBIX file with some safe sample data, as that will help ensure nothing is lost in translation.

Cheers,
Steve.

let
    Source = Csv.Document(File.Contents("C:\Users\Dale\Desktop\PaypallALL.CSV"),[Delimiter=",", Columns=38, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Time", type time}, {"Time zone", type text}, {"Name", type text}, {"Type", type text}, {"Status", type text}, {"Currency", type text}, {"Gross", type number}, {"Fee", type number}, {"Net", type number}, {"From Email Address", type text}, {"To Email Address", type text}, {"Transaction ID", type text}, {"Delivery Address", type text}, {"Address Status", type text}, {"Item Title", type text}, {"Item ID", type text}, {"Postage and Packaging Amount", Int64.Type}, {"Insurance Amount", Int64.Type}, {"VAT", Int64.Type}, {"Option 1 Name", type text}, {"Option 1 Value", type text}, {"Option 2 Name", type text}, {"Option 2 Value", type text}, {"Reference Txn ID", type text}, {"Invoice Number", Int64.Type}, {"Custom Number", type text}, {"Receipt ID", type text}, {"Balance", type number}, {"Address Line 1", type text}, {"Address Line 2/District/Neighbourhood", type text}, {"Town/City", type text}, {"County", type text}, {"Postcode", type text}, {"Country", type text}, {"Contact Phone Number", type text}, {"Subject", type text}, {"Note", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Date", "Name", "Type", "Gross", "Fee"}),
    #"Added Inflow Column" = Table.AddColumn(#"Removed Other Columns", "Inflow", each if [Gross] > 0 then [Gross] else "0" ),
    #"Added Outflow Column" = Table.AddColumn(#"Added Inflow Column", "Outflow", each if [Gross] < 0 then [Gross] else [Fee] ),
    #"Changed InflowOutflow Types" = Table.TransformColumnTypes(#"Added Outflow Column",{{"Inflow", type number}, {"Outflow", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed InflowOutflow Types",{{"Name", "Payee"}, {"Type", "Memo"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Gross", "Fee"})
in
    #"Removed Columns"

[thorrrr]

HI Steve

Sorry for the delay been away on business for a few days. It worked a treat can i thank you for all your help on the matter and your patience. Paypal export is such a nightmare here in the UK and their data entries are nothing like banks

Cheers Again