Forum Discussion
Remove rows with null when there are multiple rows with the same ID
- In my data, I have a list of IDs and a results column with the results for that ID.
- All IDs are shown at least once with a null value in the results column.
- However, some IDs are shown multiple times because they have actual values in the results column.
- When an ID is listed multiple times (and only then), I want to delete the row where the results value is null.
Example data
| ID | Results |
| 1 | |
| 2 | |
| 2 | Value1 |
| 3 | |
| 4 | |
| 5 | |
| 5 | Value2 |
| 5 | Value3 |
Desired Result
| ID | Results |
| 1 | |
| 2 | Value1 |
| 3 | |
| 4 | |
| 5 | Value2 |
| 5 | Value3 |
here's a Power Query version
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVJQitWJVjJCYYUl5pSmGoK5xnAJEzjLFIUFVmyEyjVWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Results = _t]), #"Replaced Value" = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Results"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"Values", each let l = _[Results] in if List.Count(l)=1 then l else List.RemoveNulls(l), type list}}), #"Expanded Rows" = Table.ExpandListColumn(#"Grouped Rows", "Values") in #"Expanded Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
2 Replies
Below is a DAX table I created -
Result = var timesPerResult = ADDCOLUMNS('Table', "IDTimes", CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[ID]))) RETURN SELECTCOLUMNS(FILTER(timesPerResult, 'Table'[Results] <> "" || [IDTimes] = 1), "ID", 'Table'[ID], "Results", 'Table'[Results])the variable timesPerResult returns a table like the following:
with the idea to create a temporary column (IDTimes) that I can filter on. You can probably replicate the result in PowerQuery (though you most likely need to join with another summarised table, the logic will be the same)
Hope this helps
here's a Power Query version
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVJQitWJVjJCYYUl5pSmGoK5xnAJEzjLFIUFVmyEyjVWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Results = _t]), #"Replaced Value" = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Results"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"Values", each let l = _[Results] in if List.Count(l)=1 then l else List.RemoveNulls(l), type list}}), #"Expanded Rows" = Table.ExpandListColumn(#"Grouped Rows", "Values") in #"Expanded Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
