Auto Summary in Excel Power Query..

[sanket_sk]

Hi All,

Every day, we work with 1000+ orders, we review our performance at the end of the day.

Sharing sample base data, based on this data I prepare a summary.

Daily input.xlsx
	A	B	C	D	E
1	Date	Account Number	Order Number	AWB No	Status Desc
2	16-07-21	124457191	NRO2082409	526316950	Delivered
3	16-07-21	124457191	ANX0133253	528356358	Delivered
4	16-07-21	124457191	ANX0135355	526524738	Delayed
5	16-07-21	124457191	CBU1170817	525929527	House Locked
6	16-07-21	124457191	CBU1170794	528556375	Delivered
7	16-07-21	124457191	CBU1170794	528556376	House Locked
8	16-07-21	124457191	CBU1170785	527482099	No Time
9	16-07-21	124457191	CBU1170784	526969816	Delayed
10	16-07-21	124457191	CBU1170822	529332697	Appointment Recheduled
11	16-07-21	124457191	CBU1170789	527651114	No Time
12	16-07-21	124457191	CBU1170813	519085887	Delivered
PHILIPS_INDIA_LTD_REVERSE_MTDCo

Since the base data size is more, every day I remove previous day data and upload new (Todays) data and hence the summary which I get (shared below) is always for the day.

Book1
	A	B	C	D	E	F	G	H
11	Date	House Locked	No Responsible person	Appointment Recheduled	No Time	Delayed	Stock Over	Total Pending
12	16-07-21 0:00	108	131	123	117	132	186	797
Sheet2

I am looking for the solution where once I upload the data power query stores summary in separate query and append the same on daily basis, so that we can be able to see daily summary data in one shot even base data does not have previous days information

Please below table for the desired result of summary

Book1
	A	B	C	D	E	F	G	H
1	Date	House Locked	No Responsible person	Appointment Recheduled	No Time	Delayed	Stock Over	Total Pending
2	10-07-21 0:00	134	175	91	91	142	167	800
3	11-07-21 0:00	94	89	188	111	130	156	768
4	12-07-21 0:00	172	110	160	175	146	158	921
5	13-07-21 0:00	140	166	102	141	120	139	808
6	14-07-21 0:00	186	124	110	119	112	180	831
7	15-07-21 0:00	161	163	119	114	92	140	789
8	16-07-21 0:00	108	131	123	117	132	186	797
Sheet2

Thanks & Regards,
Sanket

 

[Tivakaran]

Hi,
I think you want something like incremental type of summary? Means you export data daily basis only but the summary will show all?? Why dont you export the data to a separate dated file (0101.xlsx, 0102.xlsx, 0103.xlsx......) and let power query import data & reflect filename as additional colum?

 

[sanket_sk]

Hi,
I think you want something like incremental type of summary? Means you export data daily basis only but the summary will show all?? Why dont you export the data to a separate dated file (0101.xlsx, 0102.xlsx, 0103.xlsx......) and let power query import data & reflect filename as additional colum?

Hi,

It's because the number of line items are huge, managing / maintaining such huge data will be difficult hence trying this way.

Sanket

 

[Tivakaran]

Owh.. ok understtod... perhaps you can change the way workbooks works? perhaps you could save as different workbook daily...rather than single workbook..

but another way is to use Power BI (my boss once told me it handles bigger data than power query) but i never used it

 

[schwiggiddy]

It sounds like you want to save your daily data into a separate file each day as suggested above. Let PQ (or PBI) look at the folder in which you're saving these files rather than a specific file. There'll be a button at the top of the column that contains the word "binary" in all rows that will combine the data. Once PQ combines it, go into the Transform Sample File query and do your summarization there for the first file. When you view the parent query, you'll have to clean up a few errors now that the sample file is in a different state, but it should give you what you're looking for.