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
Anonymous
Not applicable

Relationship for supplier coverage based on location of customers

Hi,

 

I am hoping someone can help me problem solve this, I am looking for the best way to create a relationship between supplier coverage and customer location.

 

I have a table of Suppliers extracted from our contract database.  One of the elements is coverage, which I want to create a relationship with my other table of Customers based on their location.

 

SUPPLIER NAMECOVERAGE
Supplier ANational
Supplier BNSW
Supplier C

NSW/QLD

 

CUSTOMER NAMESTATE
Customer ANSW
Customer BQLD
Customer C

VIC

 

Unfortunately, due to the format of supplier coverage, I run into two issues;

  1. When a supplier has "national" coverage, no relationship can be made, however I need this to apply to all states.
  2. When a supplier covers multiple states, the format is every state covered separated by slashes ("/").  This is picked up as unique values, not state entities separated by slashes. 

I understand I may need to do some data shaping in power quiery, but I am unsure what I need to do... any ideas please?

 

In the example above, the end result should be;

Supplier A can service all Customers.

Supplier B can only service Customer A.

Supplier C can service Customers A & B.

 

Thank you,

Dan

 

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

To create relationships between two tables, you can create a custom column like this in power query and split this column by delimiter:

= Table.AddColumn(#"Changed Type", "New COVERAGE", each if 
[COVERAGE] = "National" then Text.Combine(#"CUSTOMER"[STATE],"/"
) else [COVERAGE])

custom column.pngsplit column.png

Then create relationship between the new column with the [state] column:

relationship.png

re.png

Attached a sample file in the below, hopes to help you.

 

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

View solution in original post

7 REPLIES 7
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

To create relationships between two tables, you can create a custom column like this in power query and split this column by delimiter:

= Table.AddColumn(#"Changed Type", "New COVERAGE", each if 
[COVERAGE] = "National" then Text.Combine(#"CUSTOMER"[STATE],"/"
) else [COVERAGE])

custom column.pngsplit column.png

Then create relationship between the new column with the [state] column:

relationship.png

re.png

Attached a sample file in the below, hopes to help you.

 

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

parry2k
Super User
Super User

@Anonymous See attached, I have given both the options, with relation and without relation.

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@Anonymous you don't need to do any transformation or don't' need a relationship between these tables, add the following measure and you will get the result

 

Supplier Customer = 
VAR __seperatorforCustomer = ","
VAR __coverageRaw = SUBSTITUTE ( MAX ( Supplier[COVERAGE] ), "/", "|" )
VAR __coverageRawTable = GENERATESERIES ( 1, PATHLENGTH ( __coverageRaw ) ) 
VAR __coverageTable = GENERATE ( __coverageRawTable, ROW ( "@Coverage", PATHITEM ( __coverageRaw, [Value], 0 ) ) )
VAR __coverages = SELECTCOLUMNS ( __coverageTable, "@Coverage", [@Coverage] )
VAR __coverageCustomers =  CALCULATETABLE ( VALUES ( Customer[CUSTOMER NAME] ), TREATAS ( __coverages, Customer[STATE] ) )
RETURN
IF ( MAX ( Supplier[COVERAGE] ) = "NATIONAL",
    CONCATENATEX ( 
        VALUES ( Customer[CUSTOMER NAME] ), 
        Customer[CUSTOMER NAME], 
        __seperatorforCustomer
    ), ---or change this line to "All customers"
    CONCATENATEX (
        __coverageCustomers,
        [CUSTOMER NAME],
        __seperatorforCustomer
    )
)

 

and here is the output:

 

parry2k_0-1610419530664.png

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thanks for the suggestion @parry2k ,

 

Ideally I would like to create a relationship so that when looking at customers for suppliers, customers outside the coverage will automatically be excluded, and vice-versa for suppliers used by customers.

 

The measure works for this one solution, however does not work when it comes to filtering, which is why I need the relationship setup.

 

Cheers,

Daniel

Ashish_Mathur
Super User
Super User

Hi,

Create a 2 column by 2 rows table (the first is the header row) with only National appearing in the second cell of the first column.  In the right hand side cell, list all states seperated by /.  Using the Query Editor, add the second column of the new table to the first table.  So in the third column of the first table, against National, you will have / seperated States in a single cell.  In the Query Editor, populate all blank cells in the third column of the first table with the entries shown in the second column.  Split the third column by rows using / as the seperator. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur ,

 

Thank you for your quick response!

 

Is it possible for you to please show an example of what this linking new table would look like?  I am having trouble understanding your solution.

 

Thanks,

Daniel

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.