How to show the rows with more data in PowerQuery/PowerBI?

[heriberto_mb]

I'm struggling how to identify the rows that have more data based on a column, in this case: Owner, if there is no Owner at all, show the row with no owner. PowerQuery is preferable, but if that is not possible, using PowerBI

Example:

Color	State	Owner
Blue	MI
Blue	MI	Snoopy
Yellow	AZ	Charlie
Yellow	AZ	Charlie
Red	CO
Red	CO

Desired Result:

Color	State	Owner
Blue	MI	Snoopy
Yellow	AZ	Charlie
Red	CO

[BabyYoda · Accepted solution]

This is more steps. I would personally try to push this logic back to the source but it can be done in power query.

To make this work in power query I had to create another copy  of the table and join the original table to the copy.  I also had to make a key with a merge on Owner, State, and, Color in both the copy and the original  I used that to join in the merge step.  Then I pulled the toy from the joined table.

[Omid_Motamedise · Accepted solution]

you can use group by command to solve this.

use the next formula

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Color", type text}, {"State", type text}, {"Owner", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Color", "State"}, {{"Count", each Text.Combine(List.Distinct(([Owner])))}})
in
#"Grouped Rows"



result in 

[dufoq3 · Accepted solution]

Hi @heriberto_mb, check this:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfL1BBLJiUVAUilWB1W4pCgxMw9IB+fl5xdUgqUjU3Ny8suBYo5RQCIpMScHSDlnJBblZKZiUVCZX5kPMzkoNQWk1h9IpOSD9eEUjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Color = _t, State = _t, Toy = _t, Owner = _t]),
    ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Owner"}),
    GroupedRows = Table.Group(ReplacedValue, {"Color", "State"}, {{"All", each if List.Count(List.Select([Owner], (x)=> x = null)) = List.Count([Owner]) then Table.FirstN(_, 1) else Table.FirstN(Table.SelectRows(_, (x)=> x[Owner] <> null), 1), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[Omid_Motamedise · Accepted solution]

you can use group by command to solve this.

use the next formula

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Color", type text}, {"State", type text}, {"Owner", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Color", "State"}, {{"Count", each Text.Combine(List.Distinct(([Owner])))}})
in
#"Grouped Rows"



result in 

[BabyYoda · Accepted solution]

This is more steps. I would personally try to push this logic back to the source but it can be done in power query.

To make this work in power query I had to create another copy  of the table and join the original table to the copy.  I also had to make a key with a merge on Owner, State, and, Color in both the copy and the original  I used that to join in the merge step.  Then I pulled the toy from the joined table.

[BabyYoda]

Use the Group by Power Query Transformation and use a Max on Owner.  Group by the other two fields.  See screenshot

[heriberto_mb]

Thank you very much that worked well, but found another case, I will post it as a new question.

Example:

Color	State	Toy	Owner
Blue	MI	car
Blue	MI	train	Snoopy
Yellow	AZ	ball	Charlie
Yellow	AZ	yoyo
Red	CO	doll
Red	CO	doll

Desired Result: No matter what Toy is it, report only the row which has an owner if it exists.

Color	State	Toy	Owner
Blue	MI	train	Snoopy
Yellow	AZ	ball	Charlie
Red	CO	doll