Forum Discussion

heriberto_mb's avatar
heriberto_mb
Frequent Visitor
1 year ago
Solved

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

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:

ColorStateToyOwner
BlueMIcar 
BlueMItrainSnoopy
YellowAZballCharlie
YellowAZyoyo 
RedCOdoll 
RedCOdoll 

 

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

ColorStateToyOwner
BlueMItrainSnoopy
YellowAZballCharlie
RedCOdoll 
Need Help
Tips and Tricks
  • m_dekorte's avatar
    m_dekorte
    1 year ago

    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

2 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    1 year ago

    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