Merging duplicate data if there is one?

[imCosmo]

Hi,
I have a report which runs hourly collecting 2 types of data, a username and a time amount.
As it runs hourly, the data is changing all the time, however today there's been a data upset as there's a duplicate of the usernames.
It essentially looks like this

Username	Dial time
Username1	104:22:56
Username1	63:19:19

Is there a way (potentially in advanced editor as you can submit if statements) to have it so if there is a duplicate of usernames, it will combine the dial times and merge them onto one row? I didn't want to introduce a merge or delete the rows as this would introduce it as a step and keep doing it every time the data set is pulled in.

 Any advice on this would be helpful - thank you! 

[Icey]

Hi @imCosmo ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,
Icey

[imCosmo]

Hi @Icey 
Unfortunately I've had no luck with the solutions posted above; the data when creating a new column / grouping it seems to be inaccurate; although I was wondering if the fact the Dialed Time data is formatted a Text, and not Time/Duration (PowerBI seemed to not like it when reformatted).

So far I've opted for a work-around by adding steps to change all usernames to lowercase and then removing the duplicates. My only issue with this is that the data is now not fully truthful, and so if there is a solution you or anyone else is able to provide, it'd be much appreciated.

[camargos88]

@imCosmo ,

Have you tried that ? This new column handles the duration as text and convers it to duration.

Also, you have to transform the group columns to upper or lower cases. 

It might work like that.

Ricardo

[imCosmo]

Hi @camargos88 

I've attempted this yesterday, however the duration values didn't seem to make sense.
Although after trying today, I've ran into another issue:

 Apologies for the poor censoring, just need to protect my organisations data.
The error that follows is:

Thanks for your help so far also 🙂

[camargos88]

@imCosmo ,

No problem, try appling a Trim and Clean functions before this new column.

Ricardo

[imCosmo]

@camargos88 
Amazing, that's now working - thank you! Although the duplicate conflict still exists where 2 of the same username are on the report and have different times.

[camargos88 · Accepted solution]

@imCosmo ,

Now you can group it by username and sum the duration.

Before group, be sure of trim/clean and upper or low your text columns.

Another question just to clarify...

The column is expecting 3 values (hour, minute. second), I hope nobody has more than 24 hours....

however is yes, just change the code for 4 itens and change the #duration part where is 0 to reallocate the values.

Did I answer your question? Mark my post as a solution!
Ricardo

[imCosmo]

@camargos88 Wonderful that's worked, thanks.

Shockingly there are people above 24 hours!
It looks like the Dialed Time is measuring the total, D:HH:MM:SS, I'll see if I can get this data legible for the original report.
The data itself ismeasuring how long a user has been connected to the VPN... which it looks like a few people didn't disconnect or even turn their work laptop off, so their time was around 6.9 days; in 7 days... Which is impressive.

I've since shortened the report so it only has data for the past 5 days so it won't have such a high amount of hours to see and allow me to get a more accurate representation of hours of connection in the normal working week.

[amitchandak]

@imCosmo , In edit query/data transformation, right-click on column use delete duplicate.

Refer , if this can help

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data 

[imCosmo]

@amitchandak Thanks for that, I've added it as a step, but it hasn't removed the duplicate. The duplicate isn't an exact duplicate 
There's a difference in the case types - e.g. username - USErname

[camargos88]

Hi @imCosmo ,

If you delete the duplicates it will remove the line, do you want it or group the values summing the values ?

If sum:

Ricardo