How do I get Excel to be more like a database

Use this forum for any general discussion and questions about Excel
Steve_D
Posts: 50
Joined: Sun Jul 31, 2016 2:29 pm

How do I get Excel to be more like a database

Postby Steve_D » Fri Oct 28, 2016 3:51 pm

I've just gone through the exercise of building a budget process around Power Query and Power Pivot using excel spreadsheets as input for item volumes, values and COGS. To make the process work, I created a standard item table which used an item index that was used as a means of linking the different measures across customers and time. Which was all great until we discovered;
1. The Item index in the Master table differed from the slave table with "ghost" characters.
2. New Items needed to be added to the Master and then slave tables. and;
3. Item Index inconsistencies ie. Index in the master differs through inclusion or exclusion of characters not included in the slave tables.

Is there a way where the slave tables can only take the value of the index from the Master Table? and; is there a way of linking such that any changes to the Master Table Index flows through to the slave tables?

If I was doing this in a database I'd create a look up table that would be used as the input source for the index field and referential integrity would you ensure changes and inclusions would flow through the associated tables... can you do this in Excel?

MattAllington
Posts: 849
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How do I get Excel to be more like a database

Postby MattAllington » Fri Oct 28, 2016 6:10 pm

sounds interesting, but I can't visualise what is going on. Any chance you can create a small sample workbook demonstrating the problem?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

Steve_D
Posts: 50
Joined: Sun Jul 31, 2016 2:29 pm

Re: How do I get Excel to be more like a database

Postby Steve_D » Mon Oct 31, 2016 10:58 am

Not a problem.

Steve_D
Posts: 50
Joined: Sun Jul 31, 2016 2:29 pm

Re: How do I get Excel to be more like a database

Postby Steve_D » Tue Nov 01, 2016 12:54 pm

Matt,
Attached is an example of what I was trying to explain. The naming convention is straight forward.
1. The Master Table contains a list of Items which has associated attributes used to convert Forecast volumes to Reporting Qty as well as a Product categorisation hierarchy.
2. The Slave Table contains the Item ID (Index to the Master) and values x Month representing forecast/Budget volumes for the customer. and;
3. The Master Salve File is where the two files are combined to produce a calculated Reporting Qty (from the Master Table) using the product hierarchy also from the Master Table.

As I described the issues I found were when the Index Fields from the Master didn't match the slave fields (Ghost characters) or when we added new items to the model. Although this is only three spreadsheets the full blown budget system consisted of 2 countries, 10 customers and 8 measures (160 separate spreadsheets) which is a lot to try and work through if the Master/Slave index didn't match.

So the problem I'm trying to solve is to create a master file with an index field that the slave files access directly (and only that field) and can only take their value from the Master.

The only thing I can think of is a drop down list which derives its values from a Vlookup of the Master File which, as soon as you say Vlookup is a solution seems counter intuitive to Power Pivot.
Attachments
Master Slave Model.xlsx
(311.54 KiB) Downloaded 34 times
Customer Fcast.xlsx
(17.49 KiB) Downloaded 34 times
AU Item List V1.0.xlsx
(188.71 KiB) Downloaded 36 times

MattAllington
Posts: 849
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How do I get Excel to be more like a database

Postby MattAllington » Tue Nov 08, 2016 10:49 am

Are you saying
1. that you want to audit the Master List to discover which data items in your forecast data are missing from the master list - so you can then go back and fix the master list? or
2. you want the model to somehow automatically handle it if there is no master record?

I think 1. is the right way to manage this. I just created a power query audit tool over the top of your 2 files to find the ones that exist in 1 file and are missing in the other file (attached). You can modify this to meet your needs, but from what I can see there are 106 unique codes in both tables.

compare files.xlsx
(18.45 KiB) Downloaded 39 times
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

Steve_D
Posts: 50
Joined: Sun Jul 31, 2016 2:29 pm

Re: How do I get Excel to be more like a database

Postby Steve_D » Thu Nov 10, 2016 12:32 pm

Yeh/No, the post audit is good and helps identify incorrectly aligned indexes but, I'd rather avoid the issue altogether by having the data table insert an index item from the a source (possibly the master table). Not only would this reduce the number of translation errors but help with new products/index items that may be introduced to the Master Table which could be included to relevant slave tables.

MattAllington
Posts: 849
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How do I get Excel to be more like a database

Postby MattAllington » Thu Nov 10, 2016 1:06 pm

I think you can do that. Join the data table with the master table no find the missing indexes. Save this as a connection. Then append this table to your master table maybe with a description like Misisng Index or similar. Could that work?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

vw_golf_mk3
Posts: 1
Joined: Sun Jul 31, 2016 8:45 pm

Re: How do I get Excel to be more like a database

Postby vw_golf_mk3 » Sat Nov 12, 2016 10:47 pm

Hi Steve,

To be reminded/informed of ghost characters VALUES function might be feasible way to fetch all item key from cubes. That way, you can find all the unique names/keys/indexes. Most importantly, at one time there can be ONLY one description to ONLY one item code. You can then use gateway to refresh quite frequently to create this master table. But I cannot think of a way to get the frequently refreshed master table converted into an excel sheet, from which your forecast/budget sheet extract. Hang on.. you could probably take advantage of cube function in powerpivot so that refreshed master table is in table format.

https://www.powerpivotpro.com/2010/06/u ... owerpivot/

After all, key is the key.


Return to “Excel”

Who is online

Users browsing this forum: No registered users and 1 guest