Power Query: Show only transactions starting on first FULL month

[ExcelAtEverything]

Hello,
I need to know if there is a way to do something in Power Query. Looking at the screenshot of part of my Power Query below, you will see a "Date" column followed by an "Org #" column. There are currently 4 different "Org #'s" (which are basically individual stores), with new stores being added every few months. The problem I'm having is in doing comp (TY vs LY) sales calculations,

Each store grand opened on a different date, and that date has so far always been mid-month some time (never on the first of the month). If a store were to open on the 1st of a month, then it would be great and I would have no issue. But the mid-month grand-openings are causing me calculation problems with some metrics that are best calculated if startying from a fresh, clean first full month (starting from the first of a month.

What I can say with 100% certainty is that there is no way a store will be open for a day and have no sales. So for that reason, if the store (Org #) doesn't show any sales on Jan 1st of a year, then it was almost certainly not open yet until later that year But if that date isn't until July 9th for example, then the query should show all sales starting on August 1 & after (and disregard the partial-month-sales from Jul 9-July 31. If the store were to start showing sales on July 1 however, then PQ would show all sales starting on July 1 & after (because that would be a full month)

For some added color, the data for the sheet I'm building originates from a folder full of Excel files, and then it's: Excel file>Power Query>Data Model>Power Pivot. From Power Pivot, I then extract using GETPIVOTDATA to create my sales tracker. Below is a screenshot of part of my Query, followed by the steps for my Power Query, and ending with a shot of one of the Power Pivot tables using the query. Thanks!

 

[lrobbo314]

Something along these lines.

SJ_TableToExcel_Pivot (version 2).xlsb
	A	B	C
1	Date		Date
2	4/28/2021		5/1/2021
3	4/29/2021		5/2/2021
4	4/30/2021		5/3/2021
5	5/1/2021		5/4/2021
6	5/2/2021		5/5/2021
7	5/3/2021		5/6/2021
8	5/4/2021		5/7/2021
9	5/5/2021		5/8/2021
10	5/6/2021		5/9/2021
11	5/7/2021		5/10/2021
12	5/8/2021		5/11/2021
13	5/9/2021		5/12/2021
14	5/10/2021		5/13/2021
15	5/11/2021		5/14/2021
16	5/12/2021		5/15/2021
17	5/13/2021		5/16/2021
18	5/14/2021		5/17/2021
19	5/15/2021		5/18/2021
20	5/16/2021		5/19/2021
21	5/17/2021		5/20/2021
22	5/18/2021		5/21/2021
23	5/19/2021		5/22/2021
24	5/20/2021		5/23/2021
25	5/21/2021		5/24/2021
26	5/22/2021		5/25/2021
27	5/23/2021		5/26/2021
28	5/24/2021		5/27/2021
29	5/25/2021		5/28/2021
30	5/26/2021		5/29/2021
31	5/27/2021		5/30/2021
32	5/28/2021		5/31/2021
33	5/29/2021		6/1/2021
34	5/30/2021		6/2/2021
35	5/31/2021		6/3/2021
36	6/1/2021		6/4/2021
37	6/2/2021		6/5/2021
38	6/3/2021		6/6/2021
39	6/4/2021		6/7/2021
40	6/5/2021		6/8/2021
41	6/6/2021		6/9/2021
42	6/7/2021		6/10/2021
43	6/8/2021		6/11/2021
44	6/9/2021		6/12/2021
45	6/10/2021		6/13/2021
46	6/11/2021		6/14/2021
47	6/12/2021		6/15/2021
48	6/13/2021		6/16/2021
49	6/14/2021		6/17/2021
50	6/15/2021		6/18/2021
51	6/16/2021		6/19/2021
52	6/17/2021		6/20/2021
53	6/18/2021		6/21/2021
54	6/19/2021		6/22/2021
55	6/20/2021		6/23/2021
56	6/21/2021		6/24/2021
57	6/22/2021		6/25/2021
58	6/23/2021		6/26/2021
59	6/24/2021		6/27/2021
60	6/25/2021		6/28/2021
61	6/26/2021		6/29/2021
62	6/27/2021		6/30/2021
63	6/28/2021
64	6/29/2021
65	6/30/2021
66	7/1/2021
Sheet8

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Year = Date.Year(Type[Date]{0}),
    MonthNum = Table.AddColumn(Type, "Custom", each Date.Month([Date])),
    Group = Table.Group(MonthNum, {"Custom"}, {{"Dates", each _, type table [Date=nullable date, Custom=text]}, {"Count", each Table.RowCount(_), Int64.Type}}),
    Filter = Table.AddColumn(Group, "MC", each Date.DaysInMonth(#date(Year,[Custom],1))=[Count]),
    FilterRows = Table.SelectRows(Filter, each ([MC] = true)),
    Expand = Table.ExpandTableColumn(FilterRows, "Dates", {"Date"}, {"Date"}),
    ROC = Table.SelectColumns(Expand,{"Date"})
in
    ROC

 

[ExcelAtEverything]

Thanks Irobbo314! Would you be able to explain what is happening here? I can understand about 50% of it. Thx

​

 

[ExcelAtEverything]

Sorry man, I don't understand your answer. What do I do with it? Start a new step? If yes, does it matter where? I should have mentioned I'm fairly new still to Power Query, so I need to know what the code is doing (in summary), where to put it, anything I need to change or customize? I'm a little lost here. Thx

 

[ExcelAtEverything]

Thanks, but I don't think your answer will work as I described in my initial question. Sorry for not including this the first time, but please use this XL2BB in conjunction with my opening question. Hopefully this will help someone to help me figure this out. Thank you one and all for any assistance provided, and I'd be happy to provide any additional info needed to resove this, just let me know. Thanks again in advance!?

Medallion Wellness V16.xlsm
	A	B	C	D	E	F	G	H
1	Type	Date	Org #	Order ID	Primary Category	Quantity	Line Item Total	Line Profit
2	in-store	1/1/2020	237	4291459722	basketballs	1	3290	1645
3	in-store	1/12/2020	839	94849023	basketballs	1	80	40
4	in-store	1/12/2020	839	94849023	tennis balls	1	90	45
5	in-store	1/12/2020	237	4289867784	basketballs	1	5430	2715
6	in-store	1/12/2020	237	4289867784	footballs	1	5440	2720
7	in-store	1/12/2020	1193	4285932482	tennis ball	1	9730	4865
8	in-store	1/12/2020	237	4285932482	kick balls	1	9740	4870
9	in-store	1/15/2020	839	4292684362	softballs	1	2100	1050
10	in-store	1/18/2020	2238	4291833559	basketball	1	2980	1490
11	in-store	1/18/2020	839	4291833559	accessories	1	2990	1495
12	in-store	1/18/2020	1193	4287441443	tennis balls	2	8180	4090
13	in-store	1/18/2020	1193	4287248219	basketballs	1	8470	4235
14	in-store	1/19/2020	1193	4288238524	footballs	1	7210	3605
15	in-store	1/2/2020	1193	4293997534	tennis ball	2	1020	510
16	in-store	1/2/2020	237	4289948447	accessories	1	5320	2660
17	in-store	1/2/2020	237	4289948447	basketballs	1	5330	2665
18	in-store	1/2/2020	237	4289948447	tennis balls	1	5340	2670
19	in-store	1/2/2020	237	4287845101	basketballs	1	7840	3920
20	in-store	1/2/2020	2238	4287845101	footballs	1	7850	3925
21	in-store	1/21/2020	839	4293142290	bowling balls	2	1810	905
22	delivery	1/21/2020	839	4285889023	kick balls	1	9810	4905
23	delivery	1/21/2020	839	4285889023	tennis balls	1	9820	4910
24	delivery	1/21/2020	839	4285889023	basketballs	2	9830	4915
25	delivery	1/21/2020	839	4285889023	footballs	1	9840	4920
26	delivery	1/21/2020	839	4285889023	tennis ball	2	9850	4925
Sheet1

 

[lrobbo314]

The way I read the original post was that they needed to have sales for each day of the month. I'm guessing that from this last post that's not the case and that it's more just that they had sales on the first of the month?

Although, in your last example, 237's first sale is on 1/1/2020, 1193 and 2238's is on 1/2/2020, and 839 is on 1/12/2020. In this case would it be just store 237 that would be kept? Or would you keep 1193 and 2238 as well. Because earlier you said that if they were open, they would definitely have had sales.

Just need some clarification on what exactly we are doing here.

 

[ExcelAtEverything]

Sure, let me clarify as best I can. So, all of these are stores in this report are now open for business. Each was opened on a different grand opening (GO) date. The GO date varies by store, and I was not provided with the GO date for any of the stores. It's pretty easy to figure out however, because if I just look across the report, the store sales shows all zeros until the day they do their first sale. Given what these stores sell (it's not basketballs & baseballs...lol), you can rest assured that the first day that shows any sales, is their first day of being open for business. I say that because these stores would not be open for a full day without doing any sales.

The GO date is important because in order for me to do TY vs LY comp calculations correctly, I can't compare a store that has $800k in sales in March of 2021, with their prior year's March if the store GO'd on March 15th of LY, so in that case I would just want to start fresh & clean an April 1. But in order to do that, I need to somehow get Power Query to elliminate the first month of sales if it's a partial month., which in my example would be to eliminate the month of March (since it opened on March 15th).

My example XL2BB was just an example with changed up numbers, products, etc. But the column names, position, and info types are all accurate. I just can't provide the original due to sensitive company info. Ignore those dates, as they are just several clumps of 5 or 6 dates which I pasted in in random order from the query. The actual list of sales figures is massive of course, and goes back to Jan 1, 2020. There are more stores GO'ing later this year, as well as in coming years. In my sales tracker, each row shows an individual item being sold, and details about that item. I need my sales tracker to make sure that each store's sales sales numbers always starts on the first of the month. If a store shows no items being sold until some other date during the month, then those entire sales rows should be eliminated until the 1st of the next month.

Given that there may come a time when a store has to close for a day for some odd reason (fire, a crime, etc), I would prefer to make it so that if a store shows no sales on the first of the month, then eliminate all sales until the 1st of the next month. The stores are currently open 365 days a year, and if they ever start closing for New Year's Day, then I'll deal with that issue when it comes.

I hope this provides enough backround info. I wil provide any additional info you need to make this happen. Thanks so much Irobbo314!

 

[lrobbo314]

Ok, how about this then?

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Type	Date	Org #	Order ID	Primary Category	Quantity	Line Item Total	Line Profit		Type	Date	Org #	Order ID	Primary Category	Quantity	Line Item Total	Line Profit
2	in-store	1/1/2020	237	4291459722	basketballs	1	3290	1645		in-store	1/1/2020	237	4291459722	basketballs	1	3290	1645
3	in-store	1/12/2020	839	94849023	basketballs	1	80	40		in-store	1/12/2020	237	4289867784	basketballs	1	5430	2715
4	in-store	1/12/2020	839	94849023	tennis balls	1	90	45		in-store	1/12/2020	237	4289867784	footballs	1	5440	2720
5	in-store	1/12/2020	237	4289867784	basketballs	1	5430	2715		in-store	1/12/2020	237	4285932482	kick balls	1	9740	4870
6	in-store	1/12/2020	237	4289867784	footballs	1	5440	2720		in-store	1/2/2020	237	4289948447	accessories	1	5320	2660
7	in-store	1/12/2020	1193	4285932482	tennis ball	1	9730	4865		in-store	1/2/2020	237	4289948447	basketballs	1	5330	2665
8	in-store	1/12/2020	237	4285932482	kick balls	1	9740	4870		in-store	1/2/2020	237	4289948447	tennis balls	1	5340	2670
9	in-store	1/15/2020	839	4292684362	softballs	1	2100	1050		in-store	1/2/2020	237	4287845101	basketballs	1	7840	3920
10	in-store	1/18/2020	2238	4291833559	basketball	1	2980	1490
11	in-store	1/18/2020	839	4291833559	accessories	1	2990	1495
12	in-store	1/18/2020	1193	4287441443	tennis balls	2	8180	4090
13	in-store	1/18/2020	1193	4287248219	basketballs	1	8470	4235
14	in-store	1/19/2020	1193	4288238524	footballs	1	7210	3605
15	in-store	1/2/2020	1193	4293997534	tennis ball	2	1020	510
16	in-store	1/2/2020	237	4289948447	accessories	1	5320	2660
17	in-store	1/2/2020	237	4289948447	basketballs	1	5330	2665
18	in-store	1/2/2020	237	4289948447	tennis balls	1	5340	2670
19	in-store	1/2/2020	237	4287845101	basketballs	1	7840	3920
20	in-store	1/2/2020	2238	4287845101	footballs	1	7850	3925
21	in-store	1/21/2020	839	4293142290	bowling balls	2	1810	905
22	delivery	1/21/2020	839	4285889023	kick balls	1	9810	4905
23	delivery	1/21/2020	839	4285889023	tennis balls	1	9820	4910
24	delivery	1/21/2020	839	4285889023	basketballs	2	9830	4915
25	delivery	1/21/2020	839	4285889023	footballs	1	9840	4920
26	delivery	1/21/2020	839	4285889023	tennis ball	2	9850	4925
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Date", type date}, {"Org #", Int64.Type}, {"Order ID", Int64.Type}, {"Primary Category", type text}, {"Quantity", Int64.Type}, {"Line Item Total", Int64.Type}, {"Line Profit", Int64.Type}}),
    Group = Table.Group(Type, {"Org #"}, {{"Data", each _, type table [Type=nullable text, Date=nullable date, #"Org #"=nullable number, Order ID=nullable number, Primary Category=nullable text, Quantity=nullable number, Line Item Total=nullable number, Line Profit=nullable number]}, {"MinDate", each List.Min([Date]), type nullable date}}),
    TF = Table.AddColumn(Group, "Custom", each [MinDate]=Date.StartOfMonth([MinDate])),
    Filter = Table.SelectRows(TF, each ([Custom] = true)),
    RC = Table.RemoveColumns(Filter,{"MinDate", "Custom"}),
    Expand = Table.ExpandTableColumn(RC, "Data", {"Type", "Date", "Order ID", "Primary Category", "Quantity", "Line Item Total", "Line Profit"}, {"Type", "Date", "Order ID", "Primary Category", "Quantity", "Line Item Total", "Line Profit"}),
    Reorder = Table.ReorderColumns(Expand,{"Type", "Date", "Org #", "Order ID", "Primary Category", "Quantity", "Line Item Total", "Line Profit"})
in
    Reorder

 

[ExcelAtEverything]

Hey so this is the first time I'm adding M code into PQ. I know now that it goes into the advanced editor, but every time I open Advanced editor it only shows another formula already there with no option to start a new one. What am I missing? I'm trying to figure it out on the internet right now, but figured I'd ask you. Thx

 

[lrobbo314]

If you want to copy paste the steps into the advanced editor, then you will need to right click on your table and select reference, then just past all the steps except for the 'Source' step.