Existing Database, that I need to append new data to

**SprucewoodSteve** · 09-06-2016, 10:13 PM

I have a large database (8,000 Rows x 52 columns (dates)) that I need to append an additional 26 columns to. I will need to add more columns in the future as the data comes in.

My Rows definitions are based on a set number of products, listed at a set number of locations.
These locations vary from product to product, but also from date to date

i.e.
Product# Store# Date 1 Date 2 Date 3 etc. etc. etc.
Product 1 Store 1 Inventory #'s are the product of store/product/date
Product 1 Store 5
Product 2 Store 1
Product 2 Store 4
Product 2 Store 5
Product 3 Store 7
Product 3 Store 9
etc etc
etc etc

My problem is that the new data coming in (new DATE related data) has a new set of product vs. store constraints coming in.

Does anyone have any suggestions on how I can work with Excel to deal with this? I cannot copy and paste the new dates in, because the existing data is based on existing product/store configurations.
The system does not populate "zero's" automatically. i.e. the incoming data only includes information about active stores with product.

**teylyn** · 09-06-2016, 10:22 PM

Please post a simplified data sample to illustrate what you would like to achieve. Please, no 8000 rows and 52 columns. 10 rows and 5 columns will be sufficient. Explain what this really means: "My Rows definitions are based on a set number of products, listed at a set number of locations. These locations vary from product to product, but also from date to date".

**SprucewoodSteve** · 09-06-2016, 10:56 PM

I am not sure how to send the data as an attachment, but basically below if you correct for the column shift you can see what I mean.
To explain the product/location/date better:

Each location may carry different products as they choose. Each week, the product assortment can change.
So in the original database, the main column headers will not provide a placeholder to populate new data.

Original Data Set

Product Location 02-Apr-16 26-Mar-16 19-Mar-16
83790 1 0 10 0
83790 2 8 19 10
83790 5 0 5 0
111799 1 6 4 0
111799 9 0 0 1

Incoming Data Set

Product Location 23-Apr-16 16-Apr-16 09-Apr-16
83790 1 5 3 2
83790 3 6 5 3
83790 5 1 6 7
111799 7 1 5 16
111799 9 0 23 1

Desired Output

Product Location 23-Apr-16 16-Apr-16 09-Apr-16 02-Apr-16 26-Mar-16 19-Mar-16
83790 1 5 3 2 0 10 0
83790 2 0 0 0 8 19 10
83790 3 6 5 3 0 0 0
83790 5 1 6 7 0 5 0
111799 1 0 0 0 6 4 0
111799 7 1 5 16 0 0 0
111799 9 0 23 1 0 0 1

**teylyn** · 09-08-2016, 02:23 AM

The merging of the two tables can be done quite easily with Power Query. If you have a Professional Plus license, you can run Power Query, a free add-in from from Microsoft for Excel 2010 and 2013 and baked into Excel 2016 as Get & Transform.

You'd need to load each table as a connection, then merge the two tables with the "full outer" option.

The rest is then just a matter of column order. From your data sample, I created a table "Original" and a table "Incoming" and loaded them as connections to Power Query. I then merged the tables and re-ordered the columns.

The Power Query result is the green table. The data below is your data sample desired output. (I did not replace empty cells with 0 values, but that's possible, too).

2016-09-08_18-22-16.gif

Even if you do these steps manually each time you get new data, you can do them without having to write any code. The column sort could happen outside of the query, with a simple Excel sort on the column labels. In general, it would be easier if new data would got to the end of the table, so the last date is the rightmost date.

cheers, teylyn

**SprucewoodSteve** · 09-08-2016, 08:52 PM

I looked online to Microsoft and in order to get the Power Query Public it is available for Excel 2013 all versions.
If I require the Power Query Premium, I would need to buy excel Professional Plus.
See here: https://www.microsoft.com/en-ca/down....aspx?id=39379

I will likely only be using this one function at this time, so can you tell me if I require to purchase Excel 2013 Professional Plus or can I just get a home version and still have the function I need from Power Query Public?

**teylyn** · 09-08-2016, 09:25 PM

Power Query Public will do what you need.

You only need Premium if you want to work with these data sources, which are typically only available in a corporate environment, anyway.

Corporate Power BI Data Catalog, Azure-based data sources, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP BusinessObjects, Salesforce.

**SprucewoodSteve** · 09-08-2016, 09:30 PM

Thank you for your help!

**SprucewoodSteve** · 09-10-2016, 06:56 PM

Ok, so I'm in over my head a bit here I think, unless it's easier than it sounds.
For reference, I'm using Excel 2016.

I looked up loading in a connection, and it seems to be involved, requiring some sort of OBC file as a reference.
I'm not sure hwere to get that, or how to use it to reference my files.\
Is there some light reading that can show me what to do, or am I on the wrong path?

**teylyn** · 09-11-2016, 07:24 PM

Where is your data? In the current workbook? In another workbook? In another system, like Access or SQL?

Power Query in Excel 2016 is in the Data ribbon as "Get & Transform". It's different from the other query options. Click the Get & Transform drop-down, then select your data source. That will open the Power Query editor.

This page give you a starting point with learning materials. Also, there are lots of blog posts about Power Query (Get & Transform). Just don't confuse it with the old-fashioned query tools in the "Get external data" group of the ribbon.

**SprucewoodSteve** · 09-11-2016, 09:35 PM

Ok, so I'm past all that now. I've got everything running, set all my data up after a lot of hours work, only to discover a problem.

I merged two files together, and discovered that some of the data is just "lost" in the merge.

Basically, any data that does not match the columns of the first selected query of the Data merge, just dissapears!!
It's not included anywhere in the new data.
i.e., query 1 has

Product Location Date
1 5 120122
1 6 120122

Query 2 has

Product Location Date
1 5 120130
1 9 120130

The merge result is:
Product Location Date Date
1 5 120122 120130
1 6 120122 120130

1 9 120130 is just GONE

Am I using it wrong, should it be append instead of Merge? it doesn't seem to fit well either with Append.

What happened?

**teylyn** · 09-12-2016, 12:19 AM

You need to use a Full Outer join

fullOuter.png

This is the result
result.png

**SprucewoodSteve** · 09-12-2016, 10:39 AM

I do not have that option?!
I only have a radio button to "only include matching rows".
Is this done differently in Excel 2016 or has that option been deleted for some reason?

**teylyn** · 09-12-2016, 09:26 PM

The added options in the dropdown are new(ish) functionality.

Your profile says you're on Office 2013. You say you're using 2016. Which version? Which license? Which plan? The features differ. If you are on a 365 subscription plan, you should see the dropdown options, unless you are on a corporate plan that defers updates instead of installing them as they are released.

Sorry, but in this day and age, Microsoft releases new features for Power Query first to Power BI, then to the downloadable installers for Excel 2010 and 2013, followed by the 365 consumer customers, then the enterprise customers who have opted for first-release. After that, new features will eventually hit the rest of the 365 customers. If you have a standalone version of Office, you may not see new features until a service pack is released.

Don't shoot the messenger.

Existing Database, that I need to append new data to

LinkBack

Thread Tools

Rate This Thread

Display

Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Re: Existing Database, that I need to append new data to

Thread Information

Users Browsing this Thread

Similar Threads

Matching new data with existing database

[SOLVED] Append data points to existing chart

Append data to a database

Merging data from database with existing list

[SOLVED] Simple code to append numbers to the existing data in the cells

How do I paste to append instead of replace existing data?

append Data into an existing worksheet

Bookmarks

Bookmarks

Posting Permissions