Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 NAME | COVERAGE |
Supplier A | National |
Supplier B | NSW |
Supplier C | NSW/QLD |
CUSTOMER NAME | STATE |
Customer A | NSW |
Customer B | QLD |
Customer C | VIC |
Unfortunately, due to the format of supplier coverage, I run into two issues;
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
Solved! Go to Solution.
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])
Then create relationship between the new column with the [state] column:
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.
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])
Then create relationship between the new column with the [state] column:
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.
@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.
@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:
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.
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |