Remove duplicates keeping different values

[Crobelo]

HI,

I am fairly new at this, and not sure if this feature is available. 

I need to develop a PBI Dashboard that conects diferent data from multiple databases, but one of those just has available a faulty extraction that duplicates the registries given that a multiple option variable has two values checked. What I need is to remove the duplicate registries without loosing the multiple variable values that caused the duplication in the fiert place.

Below is an example of one of the duplicate registries in the original excel file.

As you can see, Div_fis and Calz_fis have two values cheked, therefore cuadrupling the registry. What I need is to merge those registries in some way, could be concatenating de duplicates only when the values are different grouping by the ID, or something like that but I don't know how to do it.

Please Help!

Thanks 

C.

PD: 

I've tried gruping by ID, and then using the table.column comand to extract the column, but it will concatenate the values that are the same, as "provincia" in the example above.

[Tad17]

Hey @Crobelo 

@MFelix is correct, you will need to go into Query Editor and select the column that has the unique identifier that you wish to keep a single line for and then select "Remove Duplicates".

If you need the total for Div_Fis & Calz_Fis since they have different numbers for each line then you will have to create a measure using a sumif formula: https://community.powerbi.com/t5/Desktop/Sumif-in-Power-BI/td-p/15457

If you need each of those lines combined in some way you will have to use measures. If you just need one of those lines you can remove duplicates. If you do not have a unique identifier column in the original excel file then I recommend making one like in this article: https://exceljet.net/formula/extract-all-matches-with-helper-column

[MFelix · Accepted solution]

Hi @Crobelo,

On the query editor select all the columns with duplicate values that you want to remove and then remive duplicate.

Check the link below.

https://support.office.com/en-us/article/remove-duplicates-power-query-d9cffc69-dc5d-4d94-8b66-72779...