MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 281|Reply: 9

Power pivot "relationships may be needed" for two

[Copy link]

1

Topics

5

Posts

21

Integral

Member

Rank: 2

Integral
21
Post on 2-6-2017 19:13:28 | All posts |Read mode
Hello all,
I'm a relatively new user of power pivot, and have been able to use it without too many problems so far but I'm currently stuck with creating a relationship between two sets of data using a single lookup table.
Data set NA comes from same-structure files located in a folder, and files are added to this folder occasionally.
Data set EA comes from same-structure files located in another folder, and files are added to this folder occasionally.
In data set NA I have codes  names, and in data set EA I have codes and amounts.  The codes appear multiple times in each data set.
I want to display the codes, names and amounts in a single pivot table.
My lookup table lists the codes as the unique value/lookup and I am trying to connect that code to each data set.
When I manually create the relationship between NA code and Lookup code, then another relationship between EA code and Lookup code, it doesn't work.  
The result is repeated/identical values in my pivot table.
Can anyone help please?
(I haven't attached the data as it's confidential/business figures)
Thanks,
Shannon
Reply

Props Report

14

Topics

1362

Posts

2928

Integral

King

Rank: 6Rank: 6

Integral
2928
Post on 2-7-2017 04:55:31 | All posts



Hi,
Which table's Code field are you using in the pivot table?
Reply Support Opposition

Props Report

14

Topics

1362

Posts

2928

Integral

King

Rank: 6Rank: 6

Integral
2928
Post on 2-7-2017 23:50:33 | All posts



Hi,
Which table's Code field are you using in the pivot table?











Reply Support Opposition

Props Report

1

Topics

5

Posts

21

Integral

Member

Rank: 2

Integral
21
 Author| Post on 2-8-2017 00:59:50 | All posts



Hi, thanks for your response!
The code is present in both data sets and is the common link between the two.  I want it to serve as a look up, but it appears multiple times in each data set. To get around the many-to-many issue, I thought I could create a lookup table with the codes as a unique list, then link the two data sets via the code on the lookup table.
I have now attached some sample data.
In reference to file NA data set, I want to use column E (AV-Work Number) as the lookup code for column AB (AV-Work Number) in the EA Data Set.  
I want the pivot table to show columns E and K from the NA data set, and columns Y and AG from EA data set.
When I create the lookup table with the list of AV-Work Numbers as unique values, the try and link that to the AV-Work Number in each data set, the relationships are created, but I think they are going the wrong way (and I can't find a way to reverse them).
Thanks again
Reply Support Opposition

Props Report

1

Topics

5

Posts

21

Integral

Member

Rank: 2

Integral
21
 Author| Post on 2-8-2017 13:19:50 | All posts



Hi, thanks for your response!
The code is present in both data sets and is the common link between the two.  I want it to serve as a look up, but it appears multiple times in each data set. To get around the many-to-many issue, I thought I could create a lookup table with the codes as a unique list, then link the two data sets via the code on the lookup table.
I have now attached some sample data.
In reference to file NA data set, I want to use column E (AV-Work Number) as the lookup code for column AB (AV-Work Number) in the EA Data Set.  
I want the pivot table to show columns E and K from the NA data set, and columns Y and AG from EA data set.
When I create the lookup table with the list of AV-Work Numbers as unique values, the try and link that to the AV-Work Number in each data set, the relationships are created, but I think they are going the wrong way (and I can't find a way to reverse them).
Thanks again




Reply Support Opposition

Props Report

14

Topics

1362

Posts

2928

Integral

King

Rank: 6Rank: 6

Integral
2928
Post on 2-8-2017 14:44:21 | All posts



M2M is rarely simple in Power Pivot. Do you have Power Query available to you as well?
Reply Support Opposition

Props Report

14

Topics

1362

Posts

2928

Integral

King

Rank: 6Rank: 6

Integral
2928
Post on 2-8-2017 21:48:11 | All posts



M2M is rarely simple in Power Pivot. Do you have Power Query available to you as well?









Reply Support Opposition

Props Report

1

Topics

5

Posts

21

Integral

Member

Rank: 2

Integral
21
 Author| Post on 2-8-2017 22:54:40 | All posts



Yes, I'm using Excel 2016 so both are available to me.  I can use Power Query at a beginners level, but I'm certain I could be utilising it better.
I've been working through some PQ tutorials to try and resolve this question but so far haven't figured it out!
Reply Support Opposition

Props Report

1

Topics

5

Posts

21

Integral

Member

Rank: 2

Integral
21
 Author| Post on 2-10-2017 12:16:14 | All posts



Yes, I'm using Excel 2016 so both are available to me.  I can use Power Query at a beginners level, but I'm certain I could be utilising it better.
I've been working through some PQ tutorials to try and resolve this question but so far haven't figured it out!




Reply Support Opposition

Props Report

14

Topics

1362

Posts

2928

Integral

King

Rank: 6Rank: 6

Integral
2928
Post on 2-10-2017 12:25:25 | All posts



Hi again,
You have multiple broadcaster values in NA for the same code. How do you intend for the amounts to be allocated across them? Simply duplicated?
Reply Support Opposition

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 9-21-2017 14:36 , Processed in 0.177167 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!