cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ecs70206 Frequent Visitor
Frequent 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
Super User
Super User

Re: Partial Matches

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



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

Proud to be a Datanaut!




ecs70206 Frequent Visitor
Frequent Visitor

Re: Partial Matches

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
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 256 members 2,742 guests
Please welcome our newest community members: