Formula:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Date", type date}, {"Account Name", type text}, {"Account Number", type text}, {"Description", type text}, {"Amount", type number}, {"Cr/Dr", type text}, {"Transaction Type", type text}, {"Credit Amount", type number}, {"Debit Amount", type number}, {"Customer Reference", type text}, {"Bank Reference", type text}, {"REMARK 01", type text}, {"REMARK 02", type text}, {"REMARK 03", type text}, {"REMARK 04", type text}, {"REMARK 05", type text}, {"REMARK 06", type text}, {"REMARK 07", type text}, {"REMARK 08", type text}, {"REMARK 09", type text}, {"REMARK 10", type text}, {"REMARK 11", type text}, {"REMARK 12", type any}, {"REMARK 13", type any}, {"REMARK 14", type any}, {"REMARK 15", type any}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Description", "Transaction Type"}, {"Transaction Type", "Type"}}),
#"Coluna Condicional Adicionada" = Table.AddColumn(#"Renamed Columns", "- Amount", each if [#"Cr/Dr"] = "DR" then - [#"Amount"] else [#"Amount"]),
#"Coluna Personalizada Adicionada" = Table.AddColumn(#"Coluna Condicional Adicionada", "ORIG CO NAME", each let splitREMARK03 = Splitter.SplitTextByDelimiter("XXXXXXXXXX", QuoteStyle.None)([REMARK 03]), splitsplitREMARK030 = Splitter.SplitTextByDelimiter("*", QuoteStyle.None)(splitREMARK03{0}?), splitREMARK04 = Splitter.SplitTextByDelimiter(" NAME=", QuoteStyle.None)([REMARK 04]), splitREMARK02 = List.Reverse(Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([REMARK 02])), splitREMARK022 = Splitter.SplitTextByDelimiter("IND ", QuoteStyle.None)([REMARK 02]), splitsplitREMARK0220 = Splitter.SplitTextByDelimiter("=", QuoteStyle.None)(splitREMARK022{0}?), splitREMARK023 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([REMARK 02]) in Text.Combine({Text.Middle(splitsplitREMARK030{0}?, 13), splitREMARK04{1}?, Text.Middle(splitREMARK02{1}?, 5), Text.Middle(splitsplitREMARK0220{0}?, 7, 1), splitREMARK023{3}?}), type text),
#"Colunas Reordenadas" = Table.ReorderColumns(#"Coluna Personalizada Adicionada",{"Transaction Date", "ORIG CO NAME", "Account Name", "Transaction Type", "- Amount", "Account Number", "Amount", "Cr/Dr", "Type", "Credit Amount", "Debit Amount", "Customer Reference", "Bank Reference", "REMARK 01", "REMARK 04", "REMARK 05", "REMARK 06", "REMARK 07", "REMARK 08", "REMARK 09", "REMARK 10", "REMARK 11", "REMARK 12", "REMARK 13", "REMARK 14", "REMARK 15", "REMARK 02", "REMARK 03"}),
#"Coluna Personalizada Adicionada1" = Table.AddColumn(#"Colunas Reordenadas", "Transaction Number", each let splitREMARK03 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([REMARK 03]), splitREMARK02 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([REMARK 02]) in Text.Combine({Text.Middle(splitREMARK03{0}?, 10), Text.Middle(splitREMARK02{0}?, 10)}), type text),
#"Colunas Removidas" = Table.RemoveColumns(#"Coluna Personalizada Adicionada1",{"REMARK 01", "REMARK 02", "REMARK 03", "REMARK 04", "REMARK 05", "REMARK 06", "REMARK 07", "REMARK 08", "REMARK 09", "REMARK 10", "REMARK 11", "REMARK 12", "REMARK 13", "REMARK 14", "REMARK 15", "Amount", "Cr/Dr", "Type", "Credit Amount", "Debit Amount", "Customer Reference"}),
#"Colunas Reordenadas1" = Table.ReorderColumns(#"Colunas Removidas",{"Transaction Date", "ORIG CO NAME", "Account Name", "Account Number", "Transaction Type", "- Amount", "Transaction Number", "Bank Reference"}),
#"Colunas Renomeadas" = Table.RenameColumns(#"Colunas Reordenadas1",{{"- Amount", "Amount"}})
in
#"Colunas Renomeadas"
Bookmarks