Building relationships in data model to leverage power pivot - circumventing unique key ID issue
When building relationships between two data tables in the data model of excel, you require a unique ID / key to relate the two datasets. This makes sense but I find it has an issue: It requires you to reduce the entirety of your dataset down to unique ID's in one of your tables so that it can do the 'one to many' relationship build. If you don't have unique ID's / key, the relationship between two tables often doesn't work. Yes you can use an intermediary table with unique ID's to link the two complete tables, but when using that intermediary table I find you are limited in what additional data rows you can bring into the combined power pivot table to provide context to the relationship. Once you start bringing in contextual rows from the separate tables, there ends up being a lot of repeated values for any numerical figure from the other table, which you are trying to aggregate. An example below of the power pivot table result (data from table 1 in blue, from table 2 in green) Product ID (Unique Key) Service Line Sales Amount COGS 169AKY T Shirts $1,000 $7,500 169AKY Sweaters $2,000 $7,500 169AKY Socks $3,000 $7,500 169AKY Turtle necks $4,000 $7,500 169AKY Pants $5,000 $7,500 COGS from table 2 also has the data broken out by Service Line (T-shirts, Sweaters, etc.), but will only show an aggregate COGS value for all service lines for each unique product ID, instead of breaking it out by Service Line. This is because the Service Line column is being inserted into the power pivot table from table 1, it breaks out sales by Service Line. If you insert the Service Line data column into the power pivot from table 2, COGS would be broken out by Service Line, and only an aggregate for sales would be produced. Ideally the power pivot would display sales AND COGS broken out by Service Line (and Product ID). Is there a best practice for building relationships (unique ID's) between data tables: Without reducing the richness / variety of columns and data in your original tables (when creating the unique ID)? Maybe 'group by' function in power query to obtain unique ID's before building the relationship? With retaining the ability to slice and dice the data by different characteristics (assuming they are present in both source tables) and have that detail presented in the power pivot. Thanks for the help! The humble Apprentice
Mail Merge malfunction
Using Word Mail Merge for Envelopes for UK/EU/World addresses with some Fields having blank entries. Address format is as this: «Title» «First_Name» «Last_Name» «Suffix» «Home_Address_Street» «StreetSecond» «Home_Address_City» «Home_Address_Postal_Code» «Home_Address_County» «Home_Address_CountryRegion» «Next Record» When I Preview all the addresses in the Recipients List they are there BUT when I go to Finish & Merge to a new document for Edit Review suddenly addresses are being skipped -randomly? Can anyone offer any clues or suggestions on how to overcome this please. I had tried to set a Rule to avoid CountryRegion printing the home UK line but it failed to function. Instead I deleted all the UK country linesCOUNTIFS Range Not Working
Hi, I'm a teacher who is trying to get data on my student's proficiency without manually counting individual numbers for 100+ students in 7 different criteria. I also can't upload the file because it contains information about my students that I cannot reveal. Just to give an idea, this is what I'm working with. I've made the columns super small because I can't get the first three columns to freeze without it freezing a whole window, so this is my solution to keep track of what my rubric is out of while I finish grading. I will make them normal again when I'm done. My formulas here are all working correctly, and I have no issues. The issue is in my Proficiency scale below. This is the formula I currently have in the active box in the image above: =COUNTIFS(D40:CU40,"<50,D40:CU40,>-1"). I didn't want it to disclude the zeroes or 49s, so I set it to one higher according to what I learned from researched on how to use COUNTIFS. From the screenshot, you see that I should have at least 1 student in the 0-49% (although it should be much higher calculating in the students who never submitted their essay but not pictured in my screenshots). However, the formula is not working, so I'm not sure what I did incorrectly. Any help would be appreciated. I need this data for Friday.
Excel help
Hello Excel Community. I would appreciate some help and support if possible with Excel. I am trying to set up a racking inspection check sheet, various aisles, bays and levels. The intention is to prioritise the severity of defects for repair/replace (Red, Amber and Green) with colour co-ordinated cell fill and record one of four types of defects against the earlier colour. Examples of defects would be, Upright, Beam, Crossbar and Pins. The first 3 defects could fall into all 3 repair priorities. Any support or guidance you could offer would be greatly appreciated. Many thanks Example
Creating Formulas but not responding as expected.. Due to format of cell?
I have a large moderately complex workbook for forecasting / projecting a financial situation for next 30 years. It references data on multiple sheets. I'm using Accounting format, but I have removed / shifted the decimal places so it shows Custom for the format. It has been working fine, but yesterday when I attempted some changes, all of a sudden when I create a formula, even something as simple as =H36 or as simple IF statement, the cell remains empty or shows 0! I just tried something in some unused cells, where I did a simple =E31, which contains the value 12%. Three of the four cells show 12% the other $ 0. The three that show 12% were probably General to start, but now show percentage. The one that didn't show 12%, was Custom, from me using it previously to test thigs. I then formatted a blank cell Accounting and shifted decimals (sand it reacts the same way, and show $ 0, not 12%. What is going on? LOL E31 is manually entered 12% I entered =E31 in each of these cells below. (It won't let me use a table here...) They started as General. The one that doesn't show 12% was preformatted Accounting but with decimals shifted, so it shows Custom. The last one I formatted as Accounting without removing / shifting the decimals and it pulled in the 12%... I'm lost. What's gone whacky with the Custom format after shifting the decimals? It has been working fine. Thanks 12% 12% $ 0 12% 12% 12%
VLOOKUP table array highlight problem for two excel files
I have two excel files. Book3 & Book2 In Book3 i type VLOOKUP there table array comes from Book2. But when by F2 I open formula the table array highlight in Book3, but actually it comes from Book2. So how to solve this problem? This is file Book3 This is file Book2
