Hi,
Since I cannot group the pivot table by week as I ticked "add this date to the to data model", what i thought was to create a weeknum and convert it into start and end date of the week. I am wondering how I can do that.
Regards,
MK
Hi,
Since I cannot group the pivot table by week as I ticked "add this date to the to data model", what i thought was to create a weeknum and convert it into start and end date of the week. I am wondering how I can do that.
Regards,
MK
Using Power Query
Excel 2016 (Windows) 32 bit
A B C D E 1Date Qty Monthname Start of Week End of Week 2 9/8/2018 3Sep 9/2/2018 9/8/2018 3 10/8/2018 5Oct 10/7/2018 10/13/2018 4 11/8/2018 1Nov 11/4/2018 11/10/2018 5 12/8/2018 5Dec 12/2/2018 12/8/2018 6 1/8/2019 6Jan 1/6/2019 1/12/2019 7 2/8/2019 7Feb 2/3/2019 2/9/2019 8 3/8/2019 3Mar 3/3/2019 3/9/2019 9 4/8/2019 4Apr 4/7/2019 4/13/2019 10 5/8/2019 3May 5/5/2019 5/11/2019 11 6/8/2019 4Jun 6/2/2019 6/8/2019 12 7/8/2019 5Jul 7/7/2019 7/13/2019 13 8/8/2019 7Aug 8/4/2019 8/10/2019 14 9/8/2019 8Sep 9/8/2019 9/14/2019 15 10/8/2019 9Oct 10/6/2019 10/12/2019 16 11/8/2019 5Nov 11/3/2019 11/9/2019 17 12/8/2019 6Dec 12/8/2019 12/14/2019 18 1/8/2020 5Jan 1/5/2020 1/11/2020 19 2/8/2020 5Feb 2/2/2020 2/8/2020 20 3/8/2020 7Mar 3/8/2020 3/14/2020 21 4/8/2020 8Apr 4/5/2020 4/11/2020 22 5/8/2020 9May 5/3/2020 5/9/2020 23 6/8/2020 6Jun 6/7/2020 6/13/2020 24 7/8/2020 5Jul 7/5/2020 7/11/2020
Sheet: Sheet2
Here is the Mcode
Please Login or Register to view this content.
Last edited by alansidman; 08-02-2020 at 08:46 AM.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thanks for the example, it works for me.
However, if I add more data in rows with different date, how do I let Excel automatically calculate the start and end of week ?
Please advise
Thanks
MK
Add the data to your original Table and then on the Data Tab, select the Refresh All Icon. Your data should update in PQ automatically and load to the sheet holding the output.
Thank you very much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks