Forum Discussion
Replacing specific duplicate values in a column
Hello,
I was looking for a formula in PowerQuery that would replace a list of duplicate values until it hit a value that was not a duplicate, and apply this formula to the entire column.
Thanks for any help!
Original -> Goal
| 1 | 1 |
| 1 | null |
| 1 | null |
| 4 | 4 |
| 4 | null |
| 5 | 5 |
| 5 | null |
| 5 | null |
| 5 | null |
| 5 | null |
| 5 | null |
| 5 | null |
| 5 | null |
| 7 | 7 |
| 7 | null |
| 8 | 8 |
| 9 | 9 |
| 9 | null |
Insert an Index column and use following formula where Data is your original column
= try if #"Added Index"[Data]{[Index]-1}=[Data] then null else [Data] otherwise [Data]See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1UElTZBIUxJJcyTSAkxawshYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Result", each try if #"Added Index"[Data]{[Index]-1}=[Data] then null else [Data] otherwise [Data]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data", "Index"}) in #"Removed Columns"
1 Reply
Insert an Index column and use following formula where Data is your original column
= try if #"Added Index"[Data]{[Index]-1}=[Data] then null else [Data] otherwise [Data]See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1UElTZBIUxJJcyTSAkxawshYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Result", each try if #"Added Index"[Data]{[Index]-1}=[Data] then null else [Data] otherwise [Data]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data", "Index"}) in #"Removed Columns"