Relationship Between 2 Data Sources - Add 1 column from 1 source to the other

[mundy727]

Hi Power BI Community,

I have the below 2 seperate data sources that look something like this:

Table 1

ID	Country	Region
1	USA	North America
2	USA	North America
3	Singapore	APAC

Table 2

ID	Country	Date
7	USA	5/1/2023
1	USA	6/12/2023
2	USA	2/12/2023
3	Singapore

My goal is - i have a table that takes the entire tab in power bi that pulls in the elements of the first table into this 1 tab, but i want to include the date column from the second table shown (which is a seperate data source) into this first table. I attempt to create a relationship between the 2 tables, and when i do this - im able to pull in the date column in the 1 table, but it seems to be only keeping the values of the second table that contain dates (or are not null). Any ID that may not find a date in the second table gets omitted from the first table. For example above, ID 3 would be removed all together in my 1 table since it did not find a match with a date in the second table. Id like to keep the value there for ID 3 and just return the date field as blank in my table 1.

Could use some guidance on how to do this. Appreciate your help!

Thank you

[mundy727]

However now when i do this (show items with no data), i am getting duplicates in my visual table. Steps I am taking are as follows below. I would like to have this column 'date' added in my visual table without pulling in duplicates of ID values.

Steps

1.) Visual table created of Table 1 shown above

2.) Create a relationship between the 2 tables

3.) Add date column into visual table (so now we have values from table 1 and table 2 together in visual)

4.) Duplicate values in the ID column now exist in the visual table.

I dont have any blanks in table 2 for dates to replace null values with 1/1/1900 in the query editor unfortunately.

Thank you!

[cassidy · Accepted solution]

You'll probably need to show another example to demonstrate your new issue.

For your last comment "I don't have any blanks in table 2 for dates", your initial post stated that you did.  I'm not sure how many rows you are dealing with, but Query Editor only shows a sampling of data and blank date fields may or may not be in your sample, regardless, you can still apply the replace values step.

[cassidy]

If you are talking about a Visual, you can choose to "Show items with no Data"

Otherwise, I'd add a Date value of some sort into Table 2 via Query Editor for all blanks, such as '1/1/1900'