Set value for all records with same ID based on column value from one record with that ID

[EaglesFly123]

I am trying to reduce the amount of data in my model and I have done all that can be done via an Odata query.

I am pulling in more data than I need, because in our application, it is possible to change the type of a record.  So I cant set my filter clause in my query to only pull in data of a particular type.  When I do this, I end up with records that do not look complete, when they have been.  So I end up having to pull more data than needed so that I can see all statuses of a record (like ID 456), and I end up with records that are not needed (987). 

ID	Status	Type	Title
123	Closed	Type A	Title - 1
123	Open	Type A	Title - 1
123	New	Type A	Title - 1
456	Closed	Type B	Title - 2
456	Open	Type B	Title - 2
456	New	Type A	Title - 2
789	Closed	Type B	ABCD - Title - 3
789	Open	Type B	ABCD - Title - 3
789	New	Type B	ABCD - Title - 3
987	Closed	Type B	Title - 4
987	Open	Type B	Title - 4
987	New	Type B	Title - 4

What I am trying to accomplish via Power Query is

• For an ID, If all values in the Type column are Type A, then set the new column to Value 1
• For an ID, If all values in the Type column are Type B & the Title begins with 'ABCD' then set the new column to value 2
• For an ID, if any value in the Type column are Type A, then set the new column to Value 1
• For an ID, if all values in the Type column are Type B & the Title does not begin with 'ABCD', then set the column to blank (so that these can be filtered in a following step)

Example Result

ID	Status	Type	Title	New Column
123	Closed	Type A	Title - 1	Value 1
123	Open	Type A	Title - 1	Value 1
123	New	Type A	Title - 1	Value 1
456	Closed	Type B	Title - 2	Value 1
456	Open	Type B	Title - 2	Value 1
456	New	Type A	Title - 2	Value 1
789	Closed	Type B	ABCD - Title - 3	Value 2
789	Open	Type B	ABCD - Title - 3	Value 2
789	New	Type B	ABCD - Title - 3	Value 2
987	Closed	Type B	Title - 4	Blank
987	Open	Type B	Title - 4	Blank
987	New	Type B	Title - 4	Blank

The first, second & last steps I have been able to get done, its the third one I am having difficulty with.  There may be a better approach as well, by just looking at the first record for each ID (with state = New) and setting subsequent records based on that.

I have been able to get something similar working using DAX and my report is giving the proper results.  However, I would like to accomplish this in Power Query as doing so would let me further reduce the Data in the report by about 80%.

[Jakinta · Accepted solution]

Try this...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcs7JL05NATJCKgtSFRxBjMySnFQFXQVDpVgdmCr/gtQ8Qmr8UstxKzExNcOwzAlJlRGSKmTLcKnBYRlEibmFJVbLHJ2cXYCKYIqNkRSj24lHKZLVOFVaWpjj9a4Jkipc3kVWg2YnkpJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, Type = _t, Title = _t]),
    #"Added Custom3" = Table.AddColumn(Source, "Test", each if Text.StartsWith([Title],"ABCD") then true else "",type logical ),
    #"Grouped Rows" = Table.Group(#"Added Custom3", {"ID"}, {{"GR", each _, type table [ID=nullable text, Status=nullable text, Type=nullable text, Title=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Values", each if List.Contains([GR][Type], "Type A")
then "Value 1" 
else try if List.ContainsAll([GR][Type], {"Type B"}) and List.AllTrue([GR][Test])
then "Value 2" else null otherwise ""),
    #"Expanded GR" = Table.ExpandTableColumn(#"Added Custom", "GR", {"Status", "Title", "Type"}, {"Status", "Title", "Type"})
in
    #"Expanded GR"

[Anonymous]

Hi Jakinta, do we have simpler dax formula if I only want to achieve the below scenario?

• For an ID, if any value in the Type column are Type A, then set the new column to Value 1