Replace Text in Column for all same Values in different Column

[Anonymous]

Hello,

I recently started using power bi and am now facing the following problem:

I import and combine several .xlsx from a folder. One column has IDs for the items in the list, where as a different column has the names. Now in the name column there are some names in english, some in german, and some just have a "-". 

Example shown below but the names are more complicated:

Unique ID	ID	Name
1	1	Banane
2	1	Banana
3	2	Apfel
4	2	Apple
5	2	-
6	3	Tomate
7	3	Tomate
8	4	Cucumber
9	4	Cucumber
10	4	-
11	4	Gurke
12	4	Gurke

I would like all IDs to have only one name -> 1:1, currently its 1:n and n is either 1, 2 or 3.

I don't care if its the english or the german name that will be "kept", as long as it ends up being one ID with one name and the name is not the "-". Example shown below:

Unique ID	ID	Name
1	1	Banana
2	1	Banana
3	2	Apple
4	2	Apple
5	2	Apple
6	3	Tomate
7	3	Tomate
8	4	Cucumber
9	4	Cucumber
10	4	Cucumber
11	4	Cucumber
12	4	Cucumber

Thanks for your help!

EDIT: Added unique ID column in table.

[Anonymous]

@Anonymous 

Can you upload a screenshot of how your tables are connected?

Kind regards,

Tobias

[Anonymous]

They are all the same, except split by month because they are too big to handle in a single file.

They all have the exact same columns etc., if thats what you mean.

EDIT: If it helps there is a unique ID column, which gives all the rows have a unique ID, and there are many more additional columns but they should not be relevant I think.