Checking multiple rows for same ID, see if a value exists in a different column

[vic24he]

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 column is populated for the ID, either it's there or it's not. I cannot filter out null values from Year because then the 2 ID would be removed as well. The ultimate goal is that I would be able to identify which IDs have no Year value. From this data set, 1 and 3 I would classify as having Year present, 2 is not present. Is there a way to do this in power query? 

[Anonymous]

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

[ThxAlot]

Easy enough,

[vic24he]

Hi - is there a power query solution? I am not using PBI with DAX, but rather dealing with a larger data set in excel using PQ. I looked at your response below and I'm not sure how power pivot is able to load the results into a data table

[Mederic]

Hello,
I am interested in this thread and the 2 solutions work very well.
Regarding the Power Pivot proposal, is it possible to load the result into an Excel sheet without using a pivot table?
As for a query where the table can be updated
Thanks in advance

Best regards

[ThxAlot]

YES and NO.

Yes for creating a refreshable pivot in a worksheet this way,

No because inherently, a pivot always imposes "group by" operation on selected columns by default.

[Mederic]

Thanks @ThxAlot  for your reply,
I had also tried using a pivot table.
Have a nice evening

[lbendlin · Accepted solution]

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"

[vic24he]

Thanks, this worked!