Owen Price Power Query Challenge Feb 5, 2024 - Brian Julius Solution

gistfile1.txt

let
    Source = DataRaw,
    AddDupe = Table.DuplicateColumn(Source, "Entry", "EntryDupe"),
    SpltToRowsByAPS = Table.ExpandListColumn(Table.TransformColumns(AddDupe, {{"EntryDupe", Splitter.SplitTextByDelimiter("APS ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "EntryDupe"),
    FilterBlanks = Table.SelectRows(SpltToRowsByAPS, each ([EntryDupe] <> "")),
    TrimEntryDuep = Table.TransformColumns(FilterBlanks,{{"EntryDupe", Text.Trim, type text}}),
    SplitAmtByRSpace = Table.SplitColumn(TrimEntryDuep, "EntryDupe", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"EntryDupe.1", "Amount"}),
    SplitDatebyRSpace = Table.RemoveColumns( Table.SplitColumn(SplitAmtByRSpace, "EntryDupe.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Deposit", "Date"}), "Entry"),
    AddAPS = Table.TransformColumns(SplitDatebyRSpace,{{"Deposit", each Text.Insert(_ , 0, "APS "), type text}}),
    SplitBySlash = Table.SplitColumn(Table.TransformColumnTypes(AddAPS, {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
    FormatDate = Table.AddColumn(SplitBySlash, "Date", each [Date.3] & "-" & Text.PadStart( [Date.2], 2, "0") & "-" & Text.PadStart( [Date.1], 2, "0")),
    Clean = Table.ReorderColumns( Table.RemoveColumns(FormatDate,{"Date.1", "Date.2", "Date.3"}), {"Deposit", "Amount", "Date"})
in
    Clean