Forum Discussion

lpearce's avatar
lpearce
Copper Contributor
Jun 21, 2023

Help with SQL when running Power BI

Good morning,

I am running a refresh for power BI , however the refresh falls over with the following error. 

 

Column 'Account' in Table 'Forecasting' contains a duplicate value 'BRC001' and this is not allowed for columns on one side of a many-to-relationship or for columns that are used as the primary key of a table.

 

I have run a smaller SQL and can see 2 lines for that account appear. how can i get SQL to combine the 2 values ?

 

I really appreciate your help

  • DuersinKurt's avatar
    DuersinKurt
    Copper Contributor
    Jun 26, 2023

    Hi lpearce,

     

    you can not just "force SQL Server" to combine those rows. There are too many questions, before you could have a fitting answer for your case. Nontheless I will try to give some hints:

     

    1. you can use a group by in your query that grabs the data from the SQL Server to your Power BI model
    2. change the realationship in your Power BI model to not use this column, but another one, that is unique, if possible
    3. Generate a relation table between the two tables in your Power BI model, where this specific value is unique, and activate cross filtering
    4. for a quick fix you could try to change the Power BI model relationship between those tables to many to many (caution, this implies a different behaveiour of the model, than you may expect)

    All hints to take with a grain of salt. Ideally #2 is a valid option for you, and you've "just" used the wrong column in your relationship. 

Resources