let
Source = Excel.CurrentWorkbook(){[Name="rngData"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"Column4", "Column5", "Column6"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
PostingDateCol = Table.SelectColumns(#"Promoted Headers",{"Posting Date"}),
#"Split Column by Delimiter" = Table.SplitColumn(PostingDateCol, "Posting Date", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Posting Date.1", "Posting Date.2", "Posting Date.3", "Posting Date.4", "Posting Date.5", "Posting Date.6", "Posting Date.7", "Posting Date.8", "Posting Date.9", "Posting Date.10", "Posting Date.11", "Posting Date.12", "Posting Date.13", "Posting Date.14", "Posting Date.15", "Posting Date.16", "Posting Date.17", "Posting Date.18", "Posting Date.19", "Posting Date.20", "Posting Date.21", "Posting Date.22", "Posting Date.23", "Posting Date.24"}),
#"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
#"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 0, 1),
PostingDate = Table.RenameColumns(#"Added Index",{{"Column1", "PostingDate"}}),
TranDateCol = Table.SelectColumns(#"Promoted Headers",{"Transaction Date"}),
#"Split Column by Delimiter1" = Table.SplitColumn(TranDateCol, "Transaction Date", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Transaction Date.1", "Transaction Date.2", "Transaction Date.3", "Transaction Date.4", "Transaction Date.5", "Transaction Date.6", "Transaction Date.7", "Transaction Date.8", "Transaction Date.9", "Transaction Date.10", "Transaction Date.11", "Transaction Date.12", "Transaction Date.13", "Transaction Date.14", "Transaction Date.15", "Transaction Date.16", "Transaction Date.17", "Transaction Date.18", "Transaction Date.19", "Transaction Date.20", "Transaction Date.21", "Transaction Date.22", "Transaction Date.23", "Transaction Date.24"}),
#"Transposed Table1" = Table.Transpose(#"Split Column by Delimiter1"),
#"Added Index1" = Table.AddIndexColumn(#"Transposed Table1", "Index", 0, 1),
TransDate = Table.RenameColumns(#"Added Index1",{{"Column1", "TransDate"}}),
TranDetailsCol = Table.SelectColumns(#"Promoted Headers",{"Transaction Details"}),
#"Split Column by Delimiter2" = Table.SplitColumn(TranDetailsCol, "Transaction Details", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Transaction Details.1", "Transaction Details.2", "Transaction Details.3", "Transaction Details.4", "Transaction Details.5", "Transaction Details.6", "Transaction Details.7", "Transaction Details.8", "Transaction Details.9", "Transaction Details.10", "Transaction Details.11", "Transaction Details.12", "Transaction Details.13", "Transaction Details.14", "Transaction Details.15", "Transaction Details.16", "Transaction Details.17", "Transaction Details.18", "Transaction Details.19", "Transaction Details.20", "Transaction Details.21", "Transaction Details.22", "Transaction Details.23", "Transaction Details.24", "Transaction Details.25", "Transaction Details.26"}),
#"Transposed Table2" = Table.Transpose(#"Split Column by Delimiter2"),
#"Removed Top Rows" = Table.Skip(#"Transposed Table2",2),
#"Added Index2" = Table.AddIndexColumn(#"Removed Top Rows", "Index", 0, 1),
TranDetails = Table.RenameColumns(#"Added Index2",{{"Column1", "TranDetails"}}),
AmtCol = Table.SelectColumns(#"Promoted Headers",{" (OMR) Amount"}),
#"Split Column by Delimiter3" = Table.SplitColumn(AmtCol, " (OMR) Amount", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {" (OMR) Amount.1", " (OMR) Amount.2", " (OMR) Amount.3", " (OMR) Amount.4", " (OMR) Amount.5", " (OMR) Amount.6", " (OMR) Amount.7", " (OMR) Amount.8", " (OMR) Amount.9", " (OMR) Amount.10", " (OMR) Amount.11", " (OMR) Amount.12", " (OMR) Amount.13", " (OMR) Amount.14", " (OMR) Amount.15", " (OMR) Amount.16", " (OMR) Amount.17", " (OMR) Amount.18", " (OMR) Amount.19", " (OMR) Amount.20", " (OMR) Amount.21", " (OMR) Amount.22", " (OMR) Amount.23", " (OMR) Amount.24", " (OMR) Amount.25"}),
#"Transposed Table3" = Table.Transpose(#"Split Column by Delimiter3"),
#"Removed Top Rows1" = Table.Skip(#"Transposed Table3",1),
#"Added Index3" = Table.AddIndexColumn(#"Removed Top Rows1", "Index", 0, 1),
Amount = Table.RenameColumns(#"Added Index3",{{"Column1", "Amount"}}),
#"Merged Queries" = Table.NestedJoin(PostingDate,{"Index"},TransDate,{"Index"},"Renamed Columns1",JoinKind.LeftOuter),
#"Expanded Renamed Columns3" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns1", {"TransDate"}, {"TransDate"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Renamed Columns3",{"Index"},#"TranDetails",{"Index"},"NewCol",JoinKind.LeftOuter),
#"Expanded NewCol" = Table.ExpandTableColumn(#"Merged Queries1", "NewCol", {"TranDetails"}, {"TranDetails"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded NewCol",{"Index"},#"Amount",{"Index"},"NewCol",JoinKind.LeftOuter),
#"Expanded NewCol1" = Table.ExpandTableColumn(#"Merged Queries2", "NewCol", {"Amount"}, {"Amount"}),
#"Split Column by Delimiter4" = Table.SplitColumn(#"Expanded NewCol1", "TranDetails", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"TranDetails.1", "FxRate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"TranDetails.1", type text}, {"FxRate", type number}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type", "TranDetails.1", Splitter.SplitTextByPositions({0, 23}, false), {"Category", "TranDetails.1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Category", type text}, {"TranDetails.1.2", type text}}),
#"Split Column by Position1" = Table.SplitColumn(#"Changed Type1", "TranDetails.1.2", Splitter.SplitTextByPositions({0, 14}, false), {"Location", "TranDetails.1.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Location", type text}, {"TranDetails.1.2.2", type text}}),
#"Split Column by Position2" = Table.SplitColumn(#"Changed Type2", "TranDetails.1.2.2", Splitter.SplitTextByPositions({0, 22}, false), {"Curr 1", "Curr 2"}),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Position2",{{"PostingDate", Text.Trim, type text}, {"TransDate", Text.Trim, type text}, {"Category", Text.Trim, type text}, {"Location", Text.Trim, type text}, {"Curr 1", Text.Trim, type text}, {"Curr 2", Text.Trim, type text}})
in
#"Trimmed Text"
