help refining lots of data via power query

Hi all,

I've recently started a job in a factory where excel is used as a tool to keep up with how far along the production line every product is. Well, another program is updated and then people like me click through hundreds of pages and update an excel workbook by coloring in cells in green to build a graph of cells. I know for sure this could be automated and way fancier looking as well but I'm pretty new to excel and get stuck. Tinkering with my own data to create a financial logbook with basic formula's and editable categories is all my experience so far.

I've made an example workbook that I'll attach that I hope captures both the data I have (the software used does "dump" all its data to another excel file) and what outcomes I am looking for. If anyone could help with my example workbook that would hopefully give me enough pointers to continue learning myself. I've also requested a training for excel, but first have to do a MS projects training.

The biggest two issues I run into at the moment;
- refining lots of data towards displaying only what I want in the specific way I want it (one step through power query, or do I need multiple steps?)
- showing changes through time. So when the data changes, how can I make excel show the difference between today and last week fi

I find it hard to explain in words so I tried to explain within the workbook example. Thanks in advance for taking a look!

Cheers, Tom

Can I confirm your Office version - is it still Excel 2016?

In your workbook:

Formula incorrect, to show intention

Please update the workbook with manually calculated expected results - non-working formulae are of little use to us.

I am looking now and may have more questions for you.

As requested, please update the sample workbook.

Done, I think. Decided to leave the part of only calculating workdays out as the example source data had weekends dates in it as well and it would require me to recalculate everything. I think this wouldn't be too difficult to adjust later.

Does it HAVE to be a PowerQuery solution?

No. I assumed that was the only way to import data from another worksheet. The software updates a read-only excel file once every night. That's my data source.
Only using power query to "copy" the data and then refining it another way instead of directly with power query is totally fine as well.

I've had a good look and I am afraid I don't understand the data and how you are trying to summarise it. Someone elwe will, I am sure.

PQ is not required to summarise data from one worksheet to another - it can be done with formulae.

Don't worry - someone will get it.

This formula based proposal for the desired result A sheet employs four helper columns (S:V) which may be moved and/or hidden for aesthetic purposes.
1. Column S is populated using: =IFERROR(INDEX(Table1[order no],MATCH(0,INDEX(COUNTIF(S$8:S8,Table1[order no]),,),)),"")
2. Column T is populated using: =IFERROR(AGGREGATE(14,6,Table1[completion date]/(Table1[article no]=B$6)/(Table1[article status]="active")/(Table1[order no]=S9),1),"")
3. Column U is populated using: =IF(T9="","",AGGREGATE(15,6,Table1[quantity]/(Table1[order no]=S9)/(Table1[completion date]=T9),1))
4. Column V is populated using: =IF(U9="","",MAX(1,SUM(U$9:U9)-1))
5. Cells A9:A13 are populated using: =IFERROR(AGGREGATE(15,6,S$9:S$14/(ROWS(A$9:A9)=V$9:V$14),1),"")
6. Cells B9:B13 are populated using: =IF($A9="","",SUMIFS(T$9:T$14,$S$9:$S$14,$A9))
7. Cells C9:C13 are populated using: =IF($A9="","",SUMIFS(U$9:U$14,$S$9:$S$14,$A9))
8. Cells E9:Q13 are populated using:

Formula:

=IF($A9="","",IF(IFERROR(INDEX(Table1[[completion date]:[completion date]],AGGREGATE(15,6,(ROW(Table1[completion date])-ROW(Table1[#Headers]))/(Table1[[order no]:[order no]]=$A9)/(Table1[[machine]:[machine]]=E$6)/(Table1[[quantity]:[quantity]]>0),COUNTIFS($E$6:E$6,E$6))),"")=$B9,$B9,""))

9. Cells E9:Q13 are conditionally formatted using: =ISNUMBER(E9)
Let us know if you have any questions.