cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PaulCo Member
Member

Trouble Creating Relationships

Hi There

 

I'm trying to create some relationships between some sales pipeline data and plan data from excel but I'm getting the following error:

 

"You can't create a relationship between these two columns because one of the columns must have unique values"

 

The SFDC Data table is a report from SFDC so I can't amend any of the fields

Opportunity NameAccount NameOpportunity Record TypeRegionFiscal PeriodClose MonthAmount
Random Name ACustomer AYEMEA 1Q4-20169/1/2016200
Random Name BCustomer AYEMEA 2Q1-201712/1/2016500
Random Name CCustomer BZNA 2Q1-201711/1/2016700
Random Name DCustomer CZNA 1Q1-201710/1/20161000

 

The plan data is manually updated in excel so I can amend this in anyway necessary.

MonthOpportunity Record TypeRegion GroupPeriodPlan Amount
9/1/2016YNA2016-Q4800
10/1/2016YNA2017-Q1900
11/1/2016YNA2017-Q11000
12/1/2016YNA2017-Q11100
9/1/2016YEMEA2016-Q4800
10/1/2016YEMEA2017-Q1900
11/1/2016YEMEA2017-Q11000
12/1/2016YEMEA2017-Q11100
9/1/2016ZNA2016-Q41000
10/1/2016ZNA2017-Q11000
11/1/2016ZNA2017-Q1800
12/1/2016ZNA2017-Q1900
9/1/2016ZEMEA2016-Q41000
10/1/2016ZEMEA2017-Q11100
11/1/2016ZEMEA2017-Q1800
12/1/2016ZEMEA2017-Q1800

 

I also have two mapping tables

 

This mapping table groups the Region in to the Region Group that I want to display

RegionRegion Group
EMEA 1EMEA
EMEA 2EMEA
NA 1NA
NA 2NA

 

This mapping rearranges the Fiscal Period so that it shows in chronological order in charts

(also note that the quarters we have use September as the start of Q1) 

Fiscal PeriodPeriod
Q4-20162016-Q4
Q1-20172017-Q1

 

I have successfully linked the mapping tables to the SFDC file and built a report sucessfully. However, when I try to link the SFDC and Plan tables I get the error message. 

 

I want to be able to slice the data by Opportunity Record Type, Region, Month and Fiscal Period so that both Plan and SFDC numbers update.

 

Any help would be greatly appreciated and if there is any way to improve what I've already done then that would also be great.

 

Thanks
Paul 

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Trouble Creating Relationships

@PaulCo


Is there a way to add the rows of the Plan data to the SFDC data so that the Opportunity Record Type, Region and Month all align and add new column for the Plan amount? I think I could slice the data correctly then. 


Yes, there is. But since you need to slice the data with columns like Opportunity Record Type, Region and Month, you need to relate these two tables based on all common columns (Month, Record Type, Region and Period) like a composite key. You can use Merge query to get the full match records. See my sample below.

 

I assume you have tables like below.

SFDC

sfdc.PNG

Plan

plan.PNG

1. Use the query below to add two columns(Region Group and Period) for SFDC table in Advanced Editor of Edit Queries.

#"Added Custom" = Table.AddColumn(#"Changed Type", "Region Group", each List.First(Text.Split([Region]," "))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Period", each List.Last(Text.Split([Fiscal Period],"-")) & "-" & List.First(Text.Split([Fiscal Period],"-")))

addcolumn.PNG

2. Use the query below to Merge Plan table and SFDC table in Advanced Editor of Edit Queries.

#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Opportunity Record Type", "Region Group", "Period", "Month"},SFDC,{"Opportunity Record Type", "Region Group", "Period", "Close Month"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Opportunity Name", "Account Name", "Amount"}, {"NewColumn.Opportunity Name", "NewColumn.Account Name", "NewColumn.Amount"})

merge.PNG

3. After click Close&Apply , you will get the Merged Plan table like below.

newPlan.PNG

4. Then you should be able to use Opportunity Record Type, Region, Month and Period to slice the data.

slice.PNG

Regards

5 REPLIES 5
BhaveshPatel Super Contributor
Super Contributor

Re: Trouble Creating Relationships

Hi There,

 

Please follow this blog post by Jeffery Wang for Handling of the Complex relationships. It will show you how to handle that error message you are getting.

 

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
PaulCo Member
Member

Re: Trouble Creating Relationships

Thanks for the response

 

But, I'm having difficulty applying the second example in the link that you gave to my problem. 

 

I think that this is because I have 2 different fields that I want to slice the data with, both the month and Opportunity Type. If I merge using one then the other doesn't match. I might be trying this incorrectly. 

 

Is there a way to add the rows of the Plan data to the SFDC data so that the Opportunity Record Type, Region and Month all align and add new column for the Plan amount? I think I could slice the data correctly then. 

 

v-ljerr-msft Super Contributor
Super Contributor

Re: Trouble Creating Relationships

@PaulCo


Is there a way to add the rows of the Plan data to the SFDC data so that the Opportunity Record Type, Region and Month all align and add new column for the Plan amount? I think I could slice the data correctly then. 


Yes, there is. But since you need to slice the data with columns like Opportunity Record Type, Region and Month, you need to relate these two tables based on all common columns (Month, Record Type, Region and Period) like a composite key. You can use Merge query to get the full match records. See my sample below.

 

I assume you have tables like below.

SFDC

sfdc.PNG

Plan

plan.PNG

1. Use the query below to add two columns(Region Group and Period) for SFDC table in Advanced Editor of Edit Queries.

#"Added Custom" = Table.AddColumn(#"Changed Type", "Region Group", each List.First(Text.Split([Region]," "))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Period", each List.Last(Text.Split([Fiscal Period],"-")) & "-" & List.First(Text.Split([Fiscal Period],"-")))

addcolumn.PNG

2. Use the query below to Merge Plan table and SFDC table in Advanced Editor of Edit Queries.

#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Opportunity Record Type", "Region Group", "Period", "Month"},SFDC,{"Opportunity Record Type", "Region Group", "Period", "Close Month"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Opportunity Name", "Account Name", "Amount"}, {"NewColumn.Opportunity Name", "NewColumn.Account Name", "NewColumn.Amount"})

merge.PNG

3. After click Close&Apply , you will get the Merged Plan table like below.

newPlan.PNG

4. Then you should be able to use Opportunity Record Type, Region, Month and Period to slice the data.

slice.PNG

Regards

PaulCo Member
Member

Re: Trouble Creating Relationships

This worked just as intended.

 

Thanks!

PaulCo Member
Member

Re: Trouble Creating Relationships

This method worked for the dataset that I provided which was intended to be a small samle.

 

When I add lines to the data I get the following message

 

"Column 'Region' in Table 'table1' contains a duplicate value 'EMEA 1' and this is not allowed for the columns on the one side of the many-to-one relationship or for the columns that are used as the primary key of the table.

 

Also, when I added new lines to the table which did not violate the above rule, It duplicated the plan amount for each of the lines, meaning that if I sum them up it give me a higher number than intended. How do I then only calculate the unique values of my original plan?

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 58 members 1,175 guests
Please welcome our newest community members: