blank values and this is not allowed for columns on the one side of a many-to-one relationship

[Fusilier2]

Hi,

I'm getting this error when refreshing:

I understand why I'm receiving it, as there are blank cells in the data I am exporting from my excel spreadsheet as there are gaps in the data.

But I don't want to filter out the empty cells as other columns have data in them that I need to use.

I've tried adding 'No Data' or 0 into the empty cells in the spreadsheet but the same error keeps appearing. Is there a fix/workaround for this that works?

[v-bmanikante]

Hi @Fusilier2 ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

[v-bmanikante]

Hi @Fusilier2 ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

[v-bmanikante]

Hi @Fusilier2 ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

[Fusilier2]

This is the table relationship

Date to date

[Deku]

Looks good, what is the problem with this?

[Gokul_G16]

Hi @Fusilier2 ,
 DO one thing, Can you please remove the blank values while importing in Sources,
Can you try like, Power Query or Other ETL tools,

If Power Query -> You can select the Home tab >  Reduce rows and > Remove the blank rows then see the result.

Cheers,
Gokulakannan. G

[Fusilier2]

When I filter out the blank values I don't get the error.

[Gokul_G16]

Hi @Fusilier2 ,

Can you please keep the all the required key column to be concatenate and write the new summarise table for your need fact or dim table, then make the relationship at your convenince.

 1. New summarize table >> Take with out blank values using dax query
 2. Do make key with required key column concatenate

3. Make correct relationship

Regards,
Gokul

[DineshYadl]

Hi @Fusilier2 

Can you please provide additonal information.

1. Data model snap.

2. check whether the blank data cloumn is a primary key.

Thanks.

[AthmakuriRekha]

Hi @Fusilier2 ,

Try to create a new column which will act as a primary key by concatenating any of the 2 columns from your spread sheet and the other dataset. Then create a relation ship using new column. 

This might help in resolving your issue.

Thanks!!

[Deku]

What type of table are you trying to join to?

[Fusilier2]

A calendar table

[Deku]

Your error says you are trying to join on the headcount (average) column. Join with the date column

[Fusilier2]

I've checked and the relationship is Calendar table (date) to Excel table (date) (One to Many). I don't understand why duplicate values are not allowed.

[danextian · Accepted solution]

The one side of a relationship must always contain unique and nonblank values. The error is not related to the dates but to the Headcount (Average) column. It's likely that an existing relationship involving this column was automatically created. To resolve this, disable the auto-detect relationships feature. If the relationship in question is not needed, delete it.

[Deku]

It should be one : many calandar : table. Should have unique dates in the calendar table, and doesn't matter if blanks or duplicates in the other table