Forum Discussion
Relationship Between 2 Data Sources - Add 1 column from 1 source to the other
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
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.
3 Replies
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'
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!
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.
