Integrating Excel Query into the forkflow

[Henceman]

I try to find ways of making a weekly data manipulation in Excel less hassle free by finding a new ways to speed the process up.

Case:

I actually dont have so much data now, but it is about 20 thousand data lines in 14 columns (every week 10-20k is added), but this will change soon.
For this dataset I have about 37 more columns which are calculated base on the figures of the original data. Also to include several pivots depending on these calculations in the same file.

As the dataset maybe only 20 thousand lines right now, well below the Excel limitations, having so much calculated cells, slows it down considerably.

I added the dataset as attachment, so its more clear what my aim is.

In the yellow you will find the data which is updated regulary and all the rest are the formulas which will derive info from this data.

Since this isnt the latest template, I already have the table formulas in place and I dont use sumif formulas but pivottable to compare values instead, but Im looking for a more elegant and faster way of handling and working with the data.

Hope you guys might have some thoughts on this.

Link to file: https://easyupload.io/c6le9k
password: dataset

 

[sandy666]

IMHO, first what you should do is (if you want use Power Query) separate source data and calculated data then do all transformation/calculation on separated tables (with key column if necessary)
now, this is one big mishmash for me

 

[Henceman]

i agree that is heavy, but the problem is, that:

1) Unfortunately I cannot derive any of the calculated data from the source system (even if I could, I doubt that someone would want to maintain complex formulas in the ERP, rather than take data as it is and manipulate it as it needs to be in Excel).
2) Data is not flawless and some of the entries in the yellow fields need to be corrected, in some rare cases, even formulas need to overwritten with exact value.
3) I only add data, I don`t take the whole dataset at once, so there needs to be history present at all times, along with the corrections done in the past.
4) I cannot hardcopy formulas from previous periods to values, because some events trigger the whole dataset to be rewritten in some cases...

 

[sandy666]

Your source data is from A to N, one and only ? (I removed formatting)
the rest is user calculation ? (on the other sheets too)

 

[sandy666]

btw. what is it: Not fdsigned in Sales ord.hist. column? typo?

all columns except Total are duplicates

 

[Henceman]

Your source data is from A to N, one and only ? (I removed formatting)
the rest is user calculation ? (on the other sheets too)

Yes.

btw. what is it: Not fdsigned in Sales ord.hist. column? typo?

Yes.

Other sheets are needed to provide base for calculation.

For example, you will see a Profit center column in the dataset, Pc mapping sheet is used to calculate corresponding unit based on the value in the data, I cannot get this information from source, as the table in Pc mapping is the sole reference.

These calculations do not work on their own based on this data, they need other sheets, you just need to look past the #value! and #N/As in this case.

 

[sandy666]

maybe with optimised source data will be easier

Company Code	Sales Org.	Profit Center	AcctfdsgGr	Subland	Customer	Column1	Column2	Column3	Period/year	Record Type	Sales ord.hist.	Site Loc Subland	Total
9997 FIRMINC fff									001.2014 January 2014	A Incoming sales order	Not fdsigned		292.5
9997 FIRMINC fff									001.2014 January 2014	A Incoming sales order	Not assigned		1374962.99
9997 FIRMINC fff									001.2014 January 2014	I Order-rel. project	1 New		90907.03
9997 FIRMINC fff									001.2014 January 2014	B Dir.posting from FI	Not assigned		76711.25
9997 FIRMINC fff									001.2014 January 2014	C Order/proj.settlemnt	Not assigned		2289894.96
9997 FIRMINC fff									001.2014 January 2014	F Billing data	Not assigned		10712
9998 FIRMINC 333									001.2014 January 2014	A Incoming sales order	Not assigned		713169.01
9998 FIRMINC 333									001.2014 January 2014	I Order-rel. project	1 New		113229.73
9998 FIRMINC 333									001.2014 January 2014	I Order-rel. project	2 Change		16564
9998 FIRMINC 333									001.2014 January 2014	B Dir.posting from FI	Not assigned		-61669.49
9998 FIRMINC 333									001.2014 January 2014	C Order/proj.settlemnt	Not assigned		953993.99
9998 FIRMINC 333									001.2014 January 2014	F Billing data	Not assigned		627240.91
9999 FIRMINC AS									001.2014 January 2014	A Incoming sales order	Not assigned		13287523.49
9999 FIRMINC AS									001.2014 January 2014	I Order-rel. project	1 New		488904
9999 FIRMINC AS									001.2014 January 2014	I Order-rel. project	2 Change		250389.29
9999 FIRMINC AS									001.2014 January 2014	B Dir.posting from FI	Not assigned		51762.81
9999 FIRMINC AS									001.2014 January 2014	C Order/proj.settlemnt	Not assigned		12971175.24
9999 FIRMINC AS									001.2014 January 2014	F Billing data	Not assigned		875

All I see is a game with text and not any numerical calculation