Forum Discussion

PaulCo's avatar
PaulCo
Helper II
9 years ago
Solved

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 ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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

    Plan

    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],"-")))

    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"})

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

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

    Regards

BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

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

  • PaulCo's avatar
    PaulCo
    Helper II
    9 years ago

    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's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      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

      Plan

      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],"-")))

      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"})

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

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

      Regards

      • PaulCo's avatar
        PaulCo
        Helper II
        9 years ago

        This worked just as intended.

         

        Thanks!