cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EaglesFly123
New Member

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

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). 

IDStatusTypeTitle
123ClosedType ATitle - 1
123OpenType ATitle - 1
123NewType ATitle - 1
456ClosedType BTitle - 2
456OpenType BTitle - 2
456NewType ATitle - 2
789ClosedType BABCD - Title - 3
789OpenType BABCD - Title - 3
789NewType BABCD - Title - 3
987ClosedType BTitle - 4
987OpenType BTitle - 4
987NewType BTitle - 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

IDStatusTypeTitleNew Column
123ClosedType ATitle - 1Value 1
123OpenType ATitle - 1Value 1
123NewType ATitle - 1Value 1
456ClosedType BTitle - 2Value 1
456OpenType BTitle - 2Value 1
456NewType ATitle - 2Value 1
789ClosedType BABCD - Title - 3Value 2
789OpenType BABCD - Title - 3Value 2
789NewType BABCD - Title - 3Value 2
987ClosedType BTitle - 4Blank
987OpenType BTitle - 4Blank
987NewType BTitle - 4Blank

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%.

1 ACCEPTED SOLUTION
Jakinta
Solution Supplier
Solution Supplier

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"

View solution in original post

1 REPLY 1
Jakinta
Solution Supplier
Solution Supplier

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"

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors