Merge large text files to later compare with another file

[Anonymous]

Hello all,

I'm a novice at this so apologies if I have posted incorrectly. I have sales data which I want to compare with data from database and extract the missing data.

But I want to transform this data into a pivot table with detail of sales per day and per file as each day data can change.

So I need to identify/compare barcode, transaction date wk1 and wk2 in all the files and Qty . as the barcode may appear in week1 but not in week2 and the sale value and qty for each barcode on a particular transaction day will differ. 

Daily data files are receeived containing a two week period fri-sat, this data is not transactional so I need to see where the duplicates of differences are.

any questions please inbox me thanks

 example data

filename	store	barcode	VAT	WK1_Transaction date	Wk1_SalesValue	W1_Qty	WK2_Transaction date	Wk2_SalesValue	Wk2_Qty
cfname_782110217_C27B6DB	4189	505326430495	20	21/05/2022	6.67	1		0	0
cfname_782110217_C27B6DB	4189	505326430495	20	22/05/2022	0	0		0	0
cfname_782110217_C27B6DB	4189	505326430495	20	23/05/2022	0	0		0	0
cfname_782110217_C27B6DB	4189	505326430495	20	24/05/2022	6.67	1		0	0
cfname_782110217_C27B6DB	4189	505326430495	20	25/05/2022	0	0		0	0
cfname_782110217_C27B6DB	4189	505326430495	20	26/05/2022	0	0		0	0
cfname_782110217_C27B6DB	4189	505326430495	20	27/05/2022	0	0		0	0
cfname_781961865_F91D7F5	4189	505326430495	20	21/05/2022	6.67	1		0	0
cfname_781961865_F91D7F5	4189	505326430495	20	22/05/2022	0	0		0	0
cfname_781961865_F91D7F5	4189	505326430495	20	23/05/2022	0	0		0	0
cfname_781961865_F91D7F5	4189	505326430495	20	24/05/2022	6.67	1		0	0
cfname_781961865_F91D7F5	4189	505326430495	20	25/05/2022	0	0		0	0
cfname_781961865_F91D7F5	4189	505326430495	20	26/05/2022	0	0		0	0
cfname_781961865_F91D7F5	4189	505326430495	20	27/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326430495	20		0	0	21/05/2022	6.67	1
cfname_781455317_ADFA50A	4189	505326430495	20		0	0	22/05/2022	0	0
cfname_781455317_ADFA50A	4189	505326430495	20		0	0	23/05/2022	0	0
cfname_781455317_ADFA50A	4189	505326430495	20		0	0	24/05/2022	6.67	1
cfname_781455317_ADFA50A	4189	505326430495	20		0	0	25/05/2022	0	0
cfname_781455317_ADFA50A	4189	505326430495	20		0	0	26/05/2022	0	0
cfname_781455317_ADFA50A	4189	505326430495	20		0	0	27/05/2022	0	0
cfname_781455317_ADFA50A	4189	505326434521	20	14/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326434521	20	15/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326434521	20	16/05/2022	8.33	1		0	0
cfname_781455317_ADFA50A	4189	505326434521	20	17/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326434521	20	18/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326434521	20	19/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326434521	20	20/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326434703	20	14/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326434703	20	15/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326434703	20	16/05/2022	13.33	1		0	0
cfname_781455317_ADFA50A	4189	505326434703	20	17/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326434703	20	18/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326434703	20	19/05/2022	0	0		0	0
cfname_781455317_ADFA50A	4189	505326434703	20	20/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326430495	20		0	0	21/05/2022	6.67	1
cfname_781316808_C6C172D	4189	505326430495	20		0	0	22/05/2022	0	0
cfname_781316808_C6C172D	4189	505326430495	20		0	0	23/05/2022	0	0
cfname_781316808_C6C172D	4189	505326430495	20		0	0	24/05/2022	6.67	1
cfname_781316808_C6C172D	4189	505326430495	20		0	0	25/05/2022	0	0
cfname_781316808_C6C172D	4189	505326430495	20		0	0	26/05/2022	0	0
cfname_781316808_C6C172D	4189	505326430495	20		0	0	27/05/2022	0	0
cfname_781316808_C6C172D	4189	505326434521	20	14/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326434521	20	15/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326434521	20	16/05/2022	8.33	1		0	0
cfname_781316808_C6C172D	4189	505326434521	20	17/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326434521	20	18/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326434521	20	19/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326434521	20	20/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326434703	20	14/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326434703	20	15/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326434703	20	16/05/2022	13.33	1		0	0
cfname_781316808_C6C172D	4189	505326434703	20	17/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326434703	20	18/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326434703	20	19/05/2022	0	0		0	0
cfname_781316808_C6C172D	4189	505326434703	20	20/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326430495	20		0	0	21/05/2022	6.67	1
cfname_781179336_1C5453F	4189	505326430495	20		0	0	22/05/2022	0	0
cfname_781179336_1C5453F	4189	505326430495	20		0	0	23/05/2022	0	0
cfname_781179336_1C5453F	4189	505326430495	20		0	0	24/05/2022	6.67	1
cfname_781179336_1C5453F	4189	505326430495	20		0	0	25/05/2022	0	0
cfname_781179336_1C5453F	4189	505326430495	20		0	0	26/05/2022	0	0
cfname_781179336_1C5453F	4189	505326430495	20		0	0	27/05/2022	0	0
cfname_781179336_1C5453F	4189	505326434521	20	14/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326434521	20	15/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326434521	20	16/05/2022	8.33	1		0	0
cfname_781179336_1C5453F	4189	505326434521	20	17/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326434521	20	18/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326434521	20	19/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326434521	20	20/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326434703	20	14/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326434703	20	15/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326434703	20	16/05/2022	13.33	1		0	0
cfname_781179336_1C5453F	4189	505326434703	20	17/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326434703	20	18/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326434703	20	19/05/2022	0	0		0	0
cfname_781179336_1C5453F	4189	505326434703	20	20/05/2022	0	0		0	0
cfname_781035288_9BE5A55	4189	505326430495	20		0	0	21/05/2022	6.67	1
cfname_781035288_9BE5A55	4189	505326430495	20		0	0	22/05/2022	0	0
cfname_781035288_9BE5A55	4189	505326430495	20		0	0	23/05/2022	0	0
cfname_781035288_9BE5A55	4189	505326430495	20		0	0	24/05/2022	0	0
cfname_781035288_9BE5A55	4189	505326430495	20		0	0	25/05/2022	0	0
cfname_781035288_9BE5A55	4189	505326430495	20		0	0	26/05/2022	0	0
cfname_781035288_9BE5A55	4189	505326430495	20		0	0	27/05/2022	0	0

[Anonymous]

The data is receievd on a daily basis any missing sales are receieved on a two week basis so at one point in time we would have complete data.

[amitchandak]

@Anonymous , I such a case we create a barcode dimension and Date table with week and compare week-on-week data. But here you have the same dates in two-column, which is not very clear

Can you share the expected output?

refer if needed

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482
https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e6f593958

[Anonymous]

the expected output is a simple output to track sales

ie Store, Transactiondate Barcode, Qty Total Sales

No duplications