Forum Discussion
Trouble Creating Relationships
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
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
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?