Error Message (Blank Values)

[Ckhanoyan]

Hi Community - 

I attempted to merge two tables using a primary column such as TicketID that the both tables already have. What I had done was that I set up was "merge queries" by using a small dataset (DailyTickets table) to merge into a mainset (MainSet table). I checked that the TicketID column from both the tables, and it does not contain any blank values. I also checked the data type for TicketID, which is currently "text" such as INC0123212.... I checked the relationship management, and I do not see any odd objects. 

I attempted to create a new column to identify blank values, and there is no blank values. I attempted to merge both the tables and still receive the error message that says "Column 'TicketID' in the MainSet Table contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table, (TicketID (25))." I also attempted to use Join Kind such as Full Outer because I want to continue to have the full mainset of all open and closed tickets as the DailyTicket table is based on the last 24 hours of updated tickets that will update the rows in the mainset. If there are new tickets that come in yesterday, that will be added to the mainset table. 

I am still struggling to figure out why I still receive this error message. 

R,

Chris

[Ckhanoyan]

I changed it to the entire dataset and I do see that there is one empty row... How do I handle this?

[Ckhanoyan]

I removed the empty part, and it seemed that I was able to merge the tables without any problems. Now, I am asking you if I merged the tables, it should be refreshed every morning that I set up the data connections (the DailyTicket small dataset is automated), and the MainSet table is not automated (but still connected to SharePoint). I just want to make sure that the small dataset will update the main table by updating rows or adding new tickets to the main table. Does that approach make sense or is there a better way to do it?

[mattiasdesmet]

If I understand that setup, you're now just merging the small table to the main table in PowerBI to get a complete view on all tickets.

This doesn't move data across, just gives you a merged view of both tables. Each time you refresh the dataset (can be scheduled in the morning) you will have a merged view on that data.

If you want to modify the Main table, you would not use PowerBI, but a dataflow for example to do so, so the changes are persisted in the main table and you can clear out the small table.

You would want to have a separate process that manages the daily changes table and one that merges those daily changes into the main table at the end of the day.

[mattiasdesmet]

You can filter it out (remove blank rows in the home ribbon), or update the source to the correct ticket value.

[FreemanZ]

hi @Ckhanoyan 

if many side of the column has items that are not present at the one side of the relationship, then blank value will be added automatically at the one side.

is it what you are facing now?

[Ckhanoyan]

I went to the data (table icon on the left side of PBI Desktop) and then I clicked on the TicketID drop down to check and see if there was a blank value. I also tried text filters by doing "contains..." and then I added space. I do not still see one blank value.

[mattiasdesmet · Accepted solution]

If you go in the Query editor and check the column profile? 
In this example I have a table with 1100 rows, first 1000 are ok. I the column distribution you'll find no blanks

However, when switching to full dataset the blank comes out int he profile:

Perhaps you're experiencing something different, but it's already a good check of your data.

Did you select the correct columns to join on in the merge? How do you define a blank for a text colum? 

[mattiasdesmet]

how did you check on the blank column values? Were you using the first 1000 records (bottom left you can change to entire data set).