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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
JRParker
Helper III
Helper III

Remove Repeating Values in a Table Visualization

So we have a basic Table visual with two fields from two tables related via one-to-many relationship.  How does one prevent the repeating value on the 'one' side while repeating the values on the 'many' side:

 

Capture1.JPG

 

Desired result:

 

Capture2.JPG

Thanks in advance; presumably straight forward but apparently not for a rookie 🙂 

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@JRParker use matrix visual, add lead_name and activity_note on rows, click drill down button, on format pane, turn off stepped layout and you will have the view you are looking for.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

@JRParker it will work, make sure you are using matrix visual, not table visual



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@JRParker use matrix visual, add lead_name and activity_note on rows, click drill down button, on format pane, turn off stepped layout and you will have the view you are looking for.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Couldn't get this to work in the following scenario when the 'many' side has two fields (Note, Activity)

 

Lead                Note                        Activity

Lead 1Note 1.1Phone Call
Lead 1Note 1.2Phone Call
Lead 1Note 1.3Phone Call
Lead 2Note 2.1Meeting
Lead 2Note 2.1Phone Call
Lead 3Note 3.1Phone Call
Lead 3Note 3.2Meeting
Lead 3Note 3.3Meeting
Lead 3Note 3.4Meeting

 

Desired outcome:

 

Lead                Note                        Activity

Lead 1Note 1.1Phone Call
 Note 1.2Phone Call
 Note 1.3Phone Call
Lead 2Note 2.1Meeting
 Note 2.1Phone Call
Lead 3Note 3.1Phone Call
 Note 3.2Meeting
 Note 3.3Meeting
 Note 3.4Meeting

 

Thanks again parry2k!

@JRParker it will work, make sure you are using matrix visual, not table visual



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Colleagues, 

The solution provided does work, but there is one problem with it:  The Matrix View does not appear to allow Horizontal scrolling.  In my situation, there are more columns to display than there is space on the page.  With a table, I can scroll to the right.  In a matrix I cannot.

 

Am I missing a setting that does allow Horizontal scrolling in the Matrix View, or is there now some other way to use the Table Visual and remove repeating information?

thank you parry2k!   ... needed to familiarize myself with drill down feature of a matrix.

 

So now that there is a flattened table due to the Left Outer join (Merged within a Query), how does one limit the number on the many side of the table? Specifically, how does one limit the number of Activities for a given Lead to some # (say 2) (sorted by date in descending order.. there is a date field; just not show in the below matrix)?  

 

 Lead Activity Matrix Visualization.JPG

Thinking the # of Activities should be limited before the two tables are joined/merged.  The Activities table (the many table) is related to the Lead table via Lead-ID; each Activity record is uniquely identified by the Activity-ID, but there are multiple records with the same Lead-ID.  How would one list the Activities for a given Lead-ID in descending order by Activity-Date, and limit the number of Activity-IDs?

 

  In an Activities query, one could duplicate the Lead-ID and the Date fields, merge the two duplicated columns, and sort the merged column.  So one would have the list in descending order, but what isn't clear, assuming this is even the correct path to take, is how to limit the number of Activities for a given Lead.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.