Forum Discussion
Solved
Checking multiple rows for same ID, see if a value exists in a different column
Hi all, I have a data set that I'm having trouble with. Data example: ID | Year 1 | null 1 | 2000 1 | null 2 | null 3 | null 3 | 2001 I need to know if the Year ...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVKK1YEwjAwMDOAcMMMIxjBGZgDVGSrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Year = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Rows", each _, type table [ID=nullable text, Year=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Year Populated", each if List.Count(List.RemoveNulls([Rows][Year]))>0 then "yes" else "no") in #"Added Custom"
I would copy the table, remove nulls from the year column, then remove duplicates from the table. Then just left outer join your original table to the new table on the ID column (the original table on the left).
--Nate