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

[dufoq3]

Hi @heriberto_mb, do not double-post please.

Solution also here.

[m_dekorte · Accepted solution]

Hi @heriberto_mb,

The most straight forward method is through a Group By on Color and State.

Give this a go.

let
    Source = Table.FromRows(
        {  
            {"Blue", "MI", "car", null},
            {"Blue", "MI", "train", "Snoopy"},
            {"Yellow", "AZ", "ball", "Charlie"},
            {"Yellow", "AZ", "yoyo", null},
            {"Red", "CO", "doll", null},
            {"Red", "CO", "doll", null}
        }, type table [Color=text, State=text, Toy=text, Owner=text]
    ),
    GroupRows = Table.Combine( 
        Table.Group( Source, {"Color", "State"}, {
            {"data", each Table.FirstN( Table.Sort(_, {"Owner", 1}), 1), 
            type table [Color=text, State=text, Toy=text, Owner=text]}
        })[data]
    )
in
    GroupRows

I hope this is helpful