Replace Multiple Values in Column with 1 Step

Power Query
1

Hi All,

I am trying to replace multiple values in a column with one value in one step. I have attached a sample file for reference. I hope the last 3 steps in the query editor can be combined. My actual data set has over 1,000 values that need to be changed into 1 value.

Thank you for your help.

Eric
Replace Multiple Values in PQ.xlsx (20.3 KB)

2

@m.eric,

I get an error message that the file you attached is corrupted when I try to open it, but I think I can provide what you need without it. Here’s a solution by the great Imke Feldmann on the MS Community forum on how to check values against a list and then return one value if the item is found in the list and a different value if the item is not found.

If that doesn’t do the trick, please resend the file and I can work out a more specific solution for you.

Thanks.

  • Brian
3

Replace Multiple Values in PQ.xlsx (20.3 KB)
@BrianJ,
Here is the file. I hope it works this time.

4

Hi @m.eric,

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

image
image1154×325 23.7 KB

I hope this is helpful

1 Like
5

Thank you Melissa. I’ll give it a try and confirm it works. I did forget to mention in my initial post that not all the original values will be changed. Your solution does look like it should work for this wrinkle.

6

Hello @m.eric. A gentle follow up if you were able to test the solution given by @Melissa.

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

7

Hello @m.eric, it’s been a while since we got a response from you. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.