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
Data Import issue???
I am working on a drift formula for items being pushed by wind and waves on the water. I imported a text file of weather data, formatted all the data to numbers, and built an "if" formula to convert the wind direction into drift direction. Wind from 360 degrees (north) would cause an item to drift 180 degrees (south). The problem is the formula sees everything as false. Is this a formatting issue with the imported text? =IF(B28<180,B28+180,B28-180)
Stockhistory returning #BLOCKED! error
I started getting this error a couple of days ago. Summary of the problem and what Excel Tech Support did but didn't fix the problem. 1. I get #BLOCKED! error with my stockhistory formula but stock data type formula is still working because I can still pull the stock price from Microsoft online repository (Bing) 2. In my local copy of Excel, the group data types has gone missing. I can retrieve it via customising ribbon but the icons for stock, currency, etc. is not there. 3. In the web version of Excel, the data types group is there but I still get the same error as (1) 4. Yesterday, the Microsoft support person reinstalled my copy of Office, checked the privacy settings in "Connected Experiences" but did not fix the problem. They said it was going to be escalated but I didn't get a call back at the agreed time. I went back to Tech Support via chat and the guy said that stockhistory function has been removed from Excel. Clearly clueless. 5. Is this a license problem?
Help Needed: Add Interactive Dashboard to Landowner Engagement Tracker (Same Sheet)
Hi all, I’ve built a basic Excel tracker to monitor engagement with landowners for a large infrastructure project. The purpose of the tracker is to: Show progress tracking of each case of for the project director to monitor progress help the Project Director Identify which cases may require statutory powers to secure access (if voluntary agreement isn’t possible) This helps our leadership team make informed decisions quickly. What I’ve got: A sheet called "LANDS RIGHT TRACKER" Columns include: Reference Number Agreement Status Case Status Risk Level (High/Medium/Low) Willingness to enter agreement Reason for refusal Flag for whether statutory powers may be needed 🛡️ All data is fictitious — names and details are placeholders, so no privacy concerns. What I need help with: my level of excel knowledge is very basic and I was hoping for some assistance to: Add an interactive dashboard and key metrics directly on the same sheet (not a separate worksheet). The below is just my suggestions as you are the expert your guidance expertise is much welcomed, happy for you to put it straight on to the tracker as I have left a section on the tracker for you to insert your items Ideally using a Pivot Table and Slicers to filter by: Case Status Risk Level Agreement Status Willingness to enter agreement https://docs.google.com/spreadsheets/d/1c2IJ-YLsTN-DZE9ltw0JR-nGVuuGzTKE/edit?usp=drivesdk&ouid=104511246399228274463&rtpof=true&sd=true
How to create a Date-Time Temperature Graph
Hi there, I was wondering if Excel is able to create a similar Date Time vs Temperature graph as shown, the green coded one? (the data was extracted from an app) I want to be able to show the Date AND Time on my Excel chart I have the raw number data but is unable to convert it into a graph similar to the one shown above, for reference the format of my raw data looks like this (below), but my raw data have three thousands results. Thank you in advance to anyone who can help me! - NonAutomate the function
Hi, I have a few thousand records of Course with ANZSIC allocation proportion. Is it possible to create an excel function to pick up the highest values within the same course whose difference of proportion with the next highest is within 0.1 and highlight it out? In this case, the first 3 records will be highlighted. Attached with an example for reference. coursecode coursename ANZSICDivisionDesc ANZSICDivisionCode ANZSICConcordanceProportion ABC123 Certificate III in Meat Processing (Food Services) Manufacturing C 0.45 ABC123 Certificate III in Meat Processing (Food Services) Retail Trade G 0.45 ABC123 Certificate III in Meat Processing (Food Services) Wholesale Trade F 0.1 BCD123 Certificate III in Meat Safety Inspection Manufacturing C 0.65 BCD123 Certificate III in Meat Safety Inspection Public Administration and Safety O 0.35 Regards EP
Power Query- filter Table by a keyword list, partial match
Hello, I have been looking for a solution for a while now to filter a table column with text (approx. 2000 rows) using a list (approx. 50) of keywords in Power Query. The list should be editable by the user in Excel and also contains partial words that I want to identify in the table. Texts in the table column are usually companies with different spellings, e.g. Amazon Germany, Amzn US, Bosch, Bsh, Glashuette, glashutte, Search terms: amazon, bsh, bosch, glash I have already found solutions for exact matches. Thank you very much for your help and the great work of the community. Best regards Oliver
