Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ecs70206
Regular Visitor

Partial Matches

Hey all,

 

Normally i'd try to puzzle these issues out myself but this one has me completely stumped.

 

Table A - a record of all journeys taken by pupils from homje to school. 14.2k rows total 11k distinct 

Table B - lots of data on schools, postcode, address,area,contact name etc 542 distinct rows 

 

I have a table that contains all of the journeys made by children in our area to thier school (Table A). This table contains only the name of the school. The column in the table that contains the data  is called just called school

 

I then have a second table (Table B) that  contains lots of information about the schools like postcode, school type, contacts details etc . The issue I have is that when I create the relationship I get lots of phantom blank values. I think because there is no corresponding value from A to B. My problem Is compounded by the fact the  the schools in table A have slight differences in spelling and spacing to the reference table.

 

I tried merging the tables and can get 6.5k out of 14000 matches.

 

I cant post the tables becuase the info is sensitive but id really appreciate any suggestions. I've put the model below though. I was wondering if there is a DAX formula to match partial values? between A and B

 

the aim is to map the flows

 

Many thanks for any advice you can offer Relationships -HTS.jpg 

 

 

2 REPLIES 2
MFelix
Super User
Super User

HI @ecs70206,

 

I don't know to what extend the slight differences in spelling and spacing you have in your model however you can create a ID column in both tables that removes the spacing and gets part of the names (first 3 letters and last 3 letters or something similar) or make everything in CAPS and then use that columns as a basis for your relationship between tables.

 

Without data it's difficult to give you an example of the changes that can be made to your information.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



hi guys,

 

further to this i've anonymised the information as much as I can so i'll post some shots hopefully it'll give a better picture of what im trying to do.

 

I have two tables - table a contains finance information and table b contains journey information. my problem is that when i count the data there are more distinct entries on the journey sheet than there are on the finance sheet. shown in the picture below. the aim is to identify the ones on the journey sheet that dont appear on the finance sheet in order to extract them and send them over to the orgintor. ive been trying to mess around with dax but i dont understand it enough yet to make it work properly - any help is appreciated

 

counts.jpg

 

I've extracted the route column from each of the master tables as new queries - the intention is to make a route key. they are set up as follows  RFJ.jpgRFF.jpg

 

the small one is the finance routes and the large one 14k rows is the journey sheet 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.