Replace Multiple Values in Column with 1 Step

Melissa · March 12, 2022, 7:20pm

Give this a go, just paste the full script into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZGxDsIgEIZfhTBDcgel1NkH0MSx6dChURNjmpYOfXuPYowaCi0DEOC7Hz7qmiNKxKXjgi+zc+fczI7tePMrEqUCAJoyQ6MAYLwRWU7TjvqQyqMRVtGmoiZPfTe07v68xnLRwl78Nx4Lc6hKI7D4K6D9UWryMg39Yxqj6QAr8Wt07O3bzb2zMeUt4zywNv5fWeeYVLbBeYjXtqQSQtl9zjHtLOs83L765psX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Number" = _t, Description = _t, Date = _t, Value = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Currency.Type}}),
    AddPeriod = Table.AddColumn(ChType, "Period", each if Date.StartOfYear([Date]) = [Date] then "Beginning" else if Date.EndOfYear([Date]) = [Date] then "End" else null, type text),
    AddSubaccount = Table.AddColumn(AddPeriod, "Subaccount", each if List.Contains( {"1111", "2222", "3333" }, Text.BeforeDelimiter([Description], "-")) then "8888" else null, type text)
in
    AddSubaccount

I hope this is helpful