Conditional Column with Power Query

[Danscot]

HI,

I have a csv download from a bank account.

Column A = Posting Date,

Column B = Transaction Type,

Column C = Description.

(Among Other Columns)

Column C contains the Transaction information in various locations based on the Transaction Type...

Date, Type, Description, Value, Balance, Account Name
26/11/2018,C/L,"'BANKOAMERIC 24NOV",-100.00,1654.50,"'CURRENT A/C"
21/11/2018,POS,"'1664 20NOV18 , SPOTIFY P09232AB54, STOCKHOLM SE",-9.99,-237.62,"'CURRENT A/C"
16/11/2018,POS,"'1664 14NOV18 , RYANAIR , HJ6Y78 , DUBLIN IE",-105.04,5.30,"'CURRENT A/C"
08/11/2018,C/L,"'INTER BANK 07NOV",-40.00,-123.92,"'CURRENT A/C"
14/11/2018,POS,"'1752 13NOV18 CD , URBAN SANDWICH , BAR , GOTHENBURG SE",-9.55,246.28,"'CURRENT A/C"
14/11/2018,POS,"'1752 13NOV18 CD , SPAR GOTHENBURG , GOTHENBURG SE",-9.78,255.83,"'CURRENT A/C"
13/11/2018,C/L,"'1664 11NOV18 , DUBLIN AIRPORT , DUBLIN IE",-300.00,-137.32,"'CURRENT A/C"

What I'm trying to do is extract a transaction date column from this based on the Type. Then a Transaction Description based on the same:

Posting Date	Transaction Date	Transaction Type	Card	Description	Value	Balance	Account Name
21/11/2018	20/11/2018	POS	1664	SPOTIFY	-9.99		CURRENT A/C
26/11/2018	24/11/2018	C/L		BANKOAMERIC	-100.00		CURRENT A/C
16/11/2018	14/11/2018	POS	1664	RYANAIR	-105.04		CURRENT A/C
08/11/2018	07/11/2018	C/L		INTER BANK	-40.00		CURRENT A/C
14/11/2018	13/11/2018	POS	1752	URBAN SANDWICH	-9.55		CURRENT A/C
14/11/2018	13/11/2018	POS	1752	SPAR GOTHENBURG	-9.78		CURRENT A/C
13/11/2018	11/11/2018	C/L	1664	DUBLIN AIRPORT	-300.00		CURRENT A/C

Previously in Excel I would have done a nested if (and) statement. To Extract the Information but have no idea where to get started in power query.

Any advice would be greatly appreciated

[Greg_Deckler · Accepted solution]

That's some really nasty formatting, here is what I came up with:

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\bank.csv"),2,"""'",null,1252),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {".1", ".2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date, Type, Description, Value, Balance, Account Name", type text}, {".1", type text}, {".2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," CD","",Replacer.ReplaceText,{".1"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{".1", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{".1", Text.Clean, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Cleaned Text", ".1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {".1.1", ".1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{".1.1", type text}, {".1.2", type date}})
in
    #"Changed Type1"

[v-juanli-msft · Accepted solution]

Hi @Danscot

Create a conditional column

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Danscot]

Ok, So building on what was posted I was able to make the below solution that works (Mostly)...

let
Source = Folder.Files("C:\Users\Finance\statements"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from statements", each #"Transform File from statements"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from statements"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from statements", Table.ColumnNames(#"Transform File from statements"(#"Sample File"))),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table Column1", each [Column1] <> null and [Column1] <> ""),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Filtered Rows4" = Table.SelectRows(#"Promoted Headers", each ([Date] <> "Date")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows4", " Description", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {" Description.1", " Description.2", " Description.3", " Description.4", " Description.5", " Description.6", " Description.7"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter"," CD","",Replacer.ReplaceText,{" Description.1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," C","",Replacer.ReplaceText,{" Description.1"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value1",{{" Description.1", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{" Description.1", Text.Clean, type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Cleaned Text",{" Description.4", " Description.5", " Description.6", " Description.7", "CURRENTAC-20181206.csv", ""}),
#"Added Conditional Column1" = Table.AddColumn(#"Removed Columns", "Description.4", each if [#" Type"] = "POS" then [#" Description.2"] else if [#" Type"] = "C/L" then [#" Description.2"] else [#" Description.1"]),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column1",{"Date", " Type", " Description.1", " Description.2", " Description.3", "Description.4", " Value", " Balance", " Account Name", " Account Number"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Reordered Columns1", " Description.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Desc.1", "Desc.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Desc.2", type date}, {"Date", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each true),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Filtered Rows1", {{"Desc.2", #date(1900, 1, 1)}}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Errors",{{"Date", "Posting Date"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Transaction Date", each if [#" Type"] = "POS" then [Desc.2] else if [#" Type"] = "C/L" then [Desc.2] else [Posting Date]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"Posting Date", "Transaction Date", " Type", "Desc.1", "Desc.2", " Description.2", " Description.3", "Description.4", " Value", " Balance", " Account Name", " Account Number"}),
#"Filtered Rows2" = Table.SelectRows(#"Reordered Columns", each ([#" Type"] <> "C/L" and [#" Type"] <> "POS")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Description.5", each [Description.4]&[#" Description.2"]),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Custom",{"Posting Date", "Transaction Date", " Type", "Description.5", "Desc.1", "Desc.2", " Description.2", " Description.3", "Description.4", " Value", " Balance", " Account Name", " Account Number"}),
#"Added Conditional Column2" = Table.AddColumn(#"Reordered Columns2", "Description", each if [Description.5] = null then [Description.4] else [Description.5]),
#"Reordered Columns3" = Table.ReorderColumns(#"Added Conditional Column2",{"Posting Date", "Transaction Date", " Type", "Description", "Description.5", "Desc.1", "Desc.2", " Description.2", " Description.3", "Description.4", " Value", " Balance", " Account Name", " Account Number"}),
#"Filtered Rows3" = Table.SelectRows(#"Reordered Columns3", each [Posting Date] <> null and [Posting Date] <> ""),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows3",{"Description.5", "Desc.1", "Desc.2", " Description.2", " Description.3", "Description.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Transaction Date", type date}})
in
#"Changed Type1"

Probably a way more efficient way to do it but this works.

[Greg_Deckler · Accepted solution]

That's some really nasty formatting, here is what I came up with:

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\bank.csv"),2,"""'",null,1252),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {".1", ".2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date, Type, Description, Value, Balance, Account Name", type text}, {".1", type text}, {".2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," CD","",Replacer.ReplaceText,{".1"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{".1", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{".1", Text.Clean, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Cleaned Text", ".1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {".1.1", ".1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{".1.1", type text}, {".1.2", type date}})
in
    #"Changed Type1"

[Danscot]

Thanks Greg,

I'm a bit confused as to where I put this solution into the query?

I forgot to mention that it's being imported as a folder with multiple CSV's so I'm setting this up through a sample file.