Forum Discussion
Extract text from one column into another
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
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"
12 Replies
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"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"
