Combining Invoice and Open Order Data Into One Report With Power Query

[Hopeless Cub Fan]

I am looking to automate the daily data feeds that drive a month end forecast report. Our projected month end number is comprised of 1) current month invoiced orders and 2) open orders to process this month. The invoiced and open order data come from two separate reports. The open order report will have missing invoice fields (invoice date, invoice month, etc) as the orders haven't processed yet.

The consolidated report columns will be based off the invoice data (top section below). I would like to map the open order data to the invoice data headers so that I can update both reports and have both of them automatically combined into one data source I can use for analysis. Example: I want to map the "Latest request date" field from the open order report to the "Shipment Date" column (below). I have included some sample data below.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
Consolidated Report Headers
Invoiced
Open
Example Below
Report 1: Invoiced Data
Invoiced
Invoiced
Report 2: Open Order Data
Open
Open
Open
Report 3: Consolidated Invoice and Open Order Data
Invoiced
Invoiced
Open
Open
Open

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Parent Customer[/TD]
[TD="align: center"]Sold-To[/TD]
[TD="align: center"]Ship-To[/TD]
[TD="align: center"]Shipment Date[/TD]
[TD="align: center"]Invoice Date[/TD]
[TD="align: center"]Invoice Day[/TD]
[TD="align: center"]Invoice Month[/TD]
[TD="align: center"]Invoice Year[/TD]
[TD="align: center"]PO #[/TD]
[TD="align: center"]Order #[/TD]
[TD="align: center"]Shipment #[/TD]
[TD="align: center"]Invoice #[/TD]
[TD="align: center"]Sales $'s[/TD]
[TD="align: center"]Qty[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Headers By Report Type[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]Parent Customer[/TD]
[TD="align: center"]Sold-To[/TD]
[TD="align: center"]Ship-To[/TD]
[TD="align: center"]Shipment Date[/TD]
[TD="align: center"]Invoice Date[/TD]
[TD="align: center"]Invoice Day[/TD]
[TD="align: center"]Invoice Month[/TD]
[TD="align: center"]Invoice Year[/TD]
[TD="align: center"]PO #[/TD]
[TD="align: center"]Order #[/TD]
[TD="align: center"]Shipment #[/TD]
[TD="align: center"]Invoice #[/TD]
[TD="align: center"]Sales $'s[/TD]
[TD="align: center"]Qty[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]Parent Customer[/TD]
[TD="align: center"]Sold-To[/TD]
[TD="align: center"]Ship-To[/TD]
[TD="align: center"]Latest request date[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Purchase order number[/TD]
[TD="align: center"]Quote/order number[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]SALES EXT[/TD]
[TD="align: center"]Open to pick qty[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Parent Customer[/TD]
[TD="align: center"]Sold-To[/TD]
[TD="align: center"]Ship-To[/TD]
[TD="align: center"]Shipment Date[/TD]
[TD="align: center"]Invoice Date[/TD]
[TD="align: center"]Invoice Day[/TD]
[TD="align: center"]Invoice Month[/TD]
[TD="align: center"]Invoice Year[/TD]
[TD="align: center"]PO #[/TD]
[TD="align: center"]Order #[/TD]
[TD="align: center"]Shipment #[/TD]
[TD="align: center"]Invoice #[/TD]
[TD="align: center"]Sales $'s[/TD]
[TD="align: center"]Qty[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1/1/2015[/TD]
[TD="align: center"]1/2/2015[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]12345[/TD]
[TD="align: center"]3456[/TD]
[TD="align: center"]5432[/TD]
[TD="align: center"]2321[/TD]
[TD="align: center"]$1,000 [/TD]
[TD="align: center"]500[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1/3/2015[/TD]
[TD="align: center"]1/4/2015[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]33323[/TD]
[TD="align: center"]2315[/TD]
[TD="align: center"]3333[/TD]
[TD="align: center"]6578[/TD]
[TD="align: center"]$5,000 [/TD]
[TD="align: center"]250[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Parent Customer[/TD]
[TD="align: center"]Sold-To[/TD]
[TD="align: center"]Ship-To[/TD]
[TD="align: center"]Latest request date[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Purchase order number[/TD]
[TD="align: center"]Quote/order number[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]SALES EXT[/TD]
[TD="align: center"]Open to pick qty[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1/27/2015[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6543[/TD]
[TD="align: center"]7768[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$3,000 [/TD]
[TD="align: center"]1000[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1/22/2015[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4352[/TD]
[TD="align: center"]5464[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$4,000 [/TD]
[TD="align: center"]400[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1/29/2015[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7856[/TD]
[TD="align: center"]3452[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$8,000 [/TD]
[TD="align: center"]700[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Parent Customer[/TD]
[TD="align: center"]Sold-To[/TD]
[TD="align: center"]Ship-To[/TD]
[TD="align: center"]Shipment Date[/TD]
[TD="align: center"]Invoice Date[/TD]
[TD="align: center"]Invoice Day[/TD]
[TD="align: center"]Invoice Month[/TD]
[TD="align: center"]Invoice Year[/TD]
[TD="align: center"]PO #[/TD]
[TD="align: center"]Order #[/TD]
[TD="align: center"]Shipment #[/TD]
[TD="align: center"]Invoice #[/TD]
[TD="align: center"]Sales $'s[/TD]
[TD="align: center"]Qty[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1/1/2015[/TD]
[TD="align: center"]1/2/2015[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]12345[/TD]
[TD="align: center"]3456[/TD]
[TD="align: center"]5432[/TD]
[TD="align: center"]2321[/TD]
[TD="align: center"]$1,000 [/TD]
[TD="align: center"]500[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1/3/2015[/TD]
[TD="align: center"]1/4/2015[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]33323[/TD]
[TD="align: center"]2315[/TD]
[TD="align: center"]3333[/TD]
[TD="align: center"]6578[/TD]
[TD="align: center"]$5,000 [/TD]
[TD="align: center"]250[/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1/27/2015[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6543[/TD]
[TD="align: center"]7768[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$3,000 [/TD]
[TD="align: center"]1000[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1/22/2015[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4352[/TD]
[TD="align: center"]5464[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$4,000 [/TD]
[TD="align: center"]400[/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1/29/2015[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7856[/TD]
[TD="align: center"]3452[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$8,000 [/TD]
[TD="align: center"]700[/TD]

</tbody>

Sheet2​

What is the best strategy to accomplish this? Thank you in advance for any help you can offer!

 

[ImkeF]

It looks as if an append-operation would give the desired results - provided that you rename the columns of your Open Orders data with the names of the invoiced data columns that they should be matched to.

I.E. "Latest Request Date" to "Shipment Date", "Purchase order number" to "PO #" ...

Please let me know if you need any further assistance here.

Imke

 

[Hopeless Cub Fan]

ImkeF,

Thanks for the reply. It does appear that this will work. As a quick follow up question, how can I append the data without duplicating both data sets? My invoice data set is ~700k rows and my open orders will be roughly 2k. I want to just bolt on the incremental open order detail into the existing invoice table vs duplicating both data sets just to combine them. How can I do this in Power Query?

 

[ImkeF]

Not sure if I got your request right - but yes, in order to append a table/query to another one, this one would need to be there - in PQ. But you don't have to load it to an Excel sheet or to the data model. Just create the connection. So do this for your shortest table: open orders.

Then the next query starts with the import of your invoice data - within this query the next step would be to append the open orders from above.

Very little dups (if at all)