Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to treat Duplicate rows as Unique Rows

Hi,

 

Greetings.

 

I have just started playing around with power BI and relatively new to the analytics space. There is a problem which I am trying to resolve from past two days.

I have imported an excel spreadsheet which has four columns - Video content, Plays, Finishes and duration of the video. I just want to make sure that the videos are listed on the x-axis and the plays and finishes on the Y-axis. The problem is that there are few videos with similar names, so the data is getting appended and reflected in my clustered column chart. I've referred to a previous post where it was suggested that we add the ID(unique column) to the query, but the solution didn't help me out.

So my question to the group is without any uniqueIDs or identifier, how can I make powerBI consider the duplicate rows as unique records and list them in X-axis with plays and finishes in Y-axis.

 

Any leads would be greatly appreciated.

 

Thanks & regards,

A Newbie seeking expert's advice.

Need Help
Tips and Tricks
Tutorial Requests
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Adding the index in the query  editor is a good first step. 

     

    You can then add a column that counts the number of occurrences of a video name and increments each time if finds another copy of the name.

     

    Occurrence = 
CALCULATE (
    COUNTA ( Data[video] ),
    FILTER (
        Data,
        Data[video] = EARLIER ( Data[video] )
            && Data[Index] <= EARLIER ( Data[Index] )
    )
)

     

     

    Then you add a column for the unique video name that used the Occurrence which will leave the video name alone on the first occurence but add the occurance number on any subsequent matches.

     

    video unique = IF ( Data[Occurrence] = 1 , Data[video] , Data[video] &" (" & Data[Occurrence] & ")" )

     

    Then just use the Video Unique in your chart.