cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

0 matches when merging two date columns between two tables

I am Trying to pull Relative day and week by merging two tables using Date column but for some reason it is returning 0 matches. Both date columns are in both tables have same format (Date).

 

I’m confused as to why it is not working.

 

Any help is greatly appreciated.

image.png

Thanks,

 

Deevs

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

I have done all possible things I could do. Please read through my previous comments.

  • Changed the date format to same format on both tables – didn’t work
  • Cleaned and Trimmed date columns to remove the unnecessary spaces and characters  - didn’t work either

 

I want to reiterate my last comments. it’s the multi relationship between Calendar date and other tables causing the issue for some reason.

 

I just duplicated the calendar table and merged it with F_ETG_Daily and it worked whereas merging with original Calendar tables doesn’t work

View solution in original post

11 REPLIES 11
Highlighted
Super User IV
Super User IV

@Deevs_411470 , I doubt your date on F_ETG_Daily has a timestamp. That is not visible because of the format . Change the data type to datetime and format having the time you can see that.

 

Create a date column like this

New Date =[Date].Date and join it with your calendar table. Make sure the calendar table has all the dates required.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

@amitchandak 

I have tried every possible option including what you have suggested. Please see below still same issue.

Can’t this be a bug in power BI ?

 

image.png

Highlighted

any one knows about a solution for this issue ?

Highlighted

Adding to that F_ETG_Daily data coming from SQL Server and  D_Calendar Data from Sap Sybase IQ  is that something preventing it from matching date column values between tables.

Highlighted

@Deevs_411470 both date formats have to be the same. Make sure they are both a true date. Can you confirm that both columns are a date format and look like this in Power Query:

edhans_0-1600833520748.png

 

You do not want a DateTime field as that wastes space in Power BI when the data loads, and you'll almost never get a match at something down to the seconds level. Date only.

If this doesn't answer the question, please post a screenshot of both fields in these tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted

@edhans @amitchandak 

I can confirm that Date column on both tables are on the same format (I .e )

 

image.png

 

I found something interesting.

D_Calendar is already connected to a Fact table call D_EGM_Daily   and D_Begin_Effctive_dates.

 

Then I have created a separate Calendar table called and D_Cal for ETG Data and merged D_EGM_Daily    with D_Cal for ETG Data and it worked .

 

image.png

Hence it implies that data cannot be merged between two tables using a date column if the table that sits date as primary key connected to multiple tables.

The ideal case would be to use the main Calendar table to merger rather than creating a duplicate of the main Calendar. Let me know your thoughts.

Thanks,

 

Deevs

Highlighted

There is nothing I am aware of in Power Query that cares about joins once the data model is loaded. Are the dates of the same locale? If one date column is MM/DD/YYYY and the other is DD/MM/YYYY they may not merge. I've not tested that theory out, but I cannot tell base on your first post and most recent post that you have a consistent format.

 

Maybe post some of the data here so we can test with it?

 

As to all of those bi-directional relationships. Do you really need all of those? Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Community Support
Community Support

@Deevs_411470 

First make sure the data type of the referenced columns are the same. Remove the non-printable characters in these two columns and try to merge again.

clean.jpg


Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted

I have done all possible things I could do. Please read through my previous comments.

  • Changed the date format to same format on both tables – didn’t work
  • Cleaned and Trimmed date columns to remove the unnecessary spaces and characters  - didn’t work either

 

I want to reiterate my last comments. it’s the multi relationship between Calendar date and other tables causing the issue for some reason.

 

I just duplicated the calendar table and merged it with F_ETG_Daily and it worked whereas merging with original Calendar tables doesn’t work

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors