Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
heriberto_mb
Frequent Visitor

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 resultNo matter what Toy is it, report only the row which has an owner if it exists.

ColorStateToyOwner
BlueMItrainSnoopy
YellowAZballCharlie
RedCOdoll 
1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

Hi @heriberto_mb,

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

m_dekorte_0-1725398930429.png

 

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

View solution in original post

2 REPLIES 2
dufoq3
Super User
Super User

Hi @heriberto_mb, do not double-post please.

 

Solution also here.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

m_dekorte
Super User
Super User

Hi @heriberto_mb,

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

m_dekorte_0-1725398930429.png

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.