Remove duplicates rows

[romovaro]

Hello

I have a question regarding duplicates rows.

I created a Microsoft Form that goes to a Sharepoint list. Some of the answers (3) can have different answers separated by commas.

Once in powerBI, I use the query editor to split the answers using comma as the delimiter.

the problem is that by doing this 3 times, I end up with triplicate rows. (one row extra for each split)

Any idea how to keep only one row and remove the other duplicates?

thanks

@jgeddes 

[MFelix · Accepted solution]

Hi @romovaro ,

If you want to show the several answers separated you will always get duplicated or triplicated rows, in this case believe that the best option is to have a dimension table for your main data for the form with Id, date and so and then create a fact table with your answers that have duplicates, triplicates and so on.

Then making use of the relational model you can have all your calculations done.

[MFelix · Accepted solution]

Hi @romovaro ,

If you want to show the several answers separated you will always get duplicated or triplicated rows, in this case believe that the best option is to have a dimension table for your main data for the form with Id, date and so and then create a fact table with your answers that have duplicates, triplicates and so on.

Then making use of the relational model you can have all your calculations done.

[romovaro · Accepted solution]

Hi MFelix Thanks for your suggestion. At the end I found a solution in Power Query to split Multiple columns into Rows ALL at once by creating custom columns using the Text.Split formula + creating Table.From Columns and Expand.)