Forum Discussion

CarstenF's avatar
CarstenF
New Member
5 years ago

Power Query duplicates data

Before data can be loaded into Power BI, they have been transformed and modified with Excel’s Power Query. I have two sets of data. After transforming them, they have the same structure and same column header. To reach this point, I used the following mapping table to assign each Entity a certain Country. Normally there is a 1:1 match, except for those entities below.

Entity

Country

187 - CROATIA

Croatia

194 - SLOVENIA

Croatia

179 - CZECH REPUBLIC

Czech

193 - SLOVAKIA

Czech

177 - HUNGARY

Hungary

577 - HUNGARY

Hungary

256 - LEBANON

Lebanon

261 - LEBANON

Lebanon

 

To combine both data sets, I have used

= Table.Combine({data_set_1, data_set_2})

in Excel’s Power Query Editor.

 

The result astonishes me: For the countries above (Coration, Czech, Hungary, Lebanon) the data of data_set_1 have been duplicated! All other countries are fine (which are not on the list above and which had a 1:1 relationship). However, I haven’t linked the mapping table as a reference to the newly created combined table in “Manage Data Model”. Data_set1 and data_set_2 have been double checked before the have been combined and they don’t contain any duplicated rows.

 

My question is: Why is Power Query using a mapping table although it has not been instructed to do so? How can I “switch” of this unintended function?

 

Any hints and comments are highly appreciated.

Thanks, Carsten

 

1 Reply

  • Jimmy801's avatar
    Jimmy801
    Community Champion

    Hello CarstenF 

     

    I cannot follow you. Why should Power Query using a mapping table if you didn't tell to do so? Could you post the M-code, some files or screenshot to understand what your queries are about and where the exact error is?

     

    BR

     

    Jimmy