Power Query: Show only transactions starting on first FULL month

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
308
Office Version
  1. 2019
Platform
  1. Windows
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!

1620059710674.png
1620061292613.png

1620062228077.png
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,175
Office Version
  1. 365
Platform
  1. Windows
Something along these lines.

SJ_TableToExcel_Pivot (version 2).xlsb
ABC
1DateDate
24/28/20215/1/2021
34/29/20215/2/2021
44/30/20215/3/2021
55/1/20215/4/2021
65/2/20215/5/2021
75/3/20215/6/2021
85/4/20215/7/2021
95/5/20215/8/2021
105/6/20215/9/2021
115/7/20215/10/2021
125/8/20215/11/2021
135/9/20215/12/2021
145/10/20215/13/2021
155/11/20215/14/2021
165/12/20215/15/2021
175/13/20215/16/2021
185/14/20215/17/2021
195/15/20215/18/2021
205/16/20215/19/2021
215/17/20215/20/2021
225/18/20215/21/2021
235/19/20215/22/2021
245/20/20215/23/2021
255/21/20215/24/2021
265/22/20215/25/2021
275/23/20215/26/2021
285/24/20215/27/2021
295/25/20215/28/2021
305/26/20215/29/2021
315/27/20215/30/2021
325/28/20215/31/2021
335/29/20216/1/2021
345/30/20216/2/2021
355/31/20216/3/2021
366/1/20216/4/2021
376/2/20216/5/2021
386/3/20216/6/2021
396/4/20216/7/2021
406/5/20216/8/2021
416/6/20216/9/2021
426/7/20216/10/2021
436/8/20216/11/2021
446/9/20216/12/2021
456/10/20216/13/2021
466/11/20216/14/2021
476/12/20216/15/2021
486/13/20216/16/2021
496/14/20216/17/2021
506/15/20216/18/2021
516/16/20216/19/2021
526/17/20216/20/2021
536/18/20216/21/2021
546/19/20216/22/2021
556/20/20216/23/2021
566/21/20216/24/2021
576/22/20216/25/2021
586/23/20216/26/2021
596/24/20216/27/2021
606/25/20216/28/2021
616/26/20216/29/2021
626/27/20216/30/2021
636/28/2021
646/29/2021
656/30/2021
667/1/2021
Sheet8


Power Query:
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

Active Member
Joined
Jan 30, 2021
Messages
308
Office Version
  1. 2019
Platform
  1. Windows
Thanks Irobbo314! Would you be able to explain what is happening here? I can understand about 50% of it. Thx

 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
308
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 30, 2021
Messages
308
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

:unsure: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
ABCDEFGH
1TypeDateOrg #Order IDPrimary CategoryQuantityLine Item TotalLine Profit
2in-store1/1/20202374291459722basketballs132901645
3in-store1/12/202083994849023basketballs18040
4in-store1/12/202083994849023tennis balls19045
5in-store1/12/20202374289867784basketballs154302715
6in-store1/12/20202374289867784footballs154402720
7in-store1/12/202011934285932482tennis ball197304865
8in-store1/12/20202374285932482kick balls197404870
9in-store1/15/20208394292684362softballs121001050
10in-store1/18/202022384291833559basketball129801490
11in-store1/18/20208394291833559accessories129901495
12in-store1/18/202011934287441443tennis balls281804090
13in-store1/18/202011934287248219basketballs184704235
14in-store1/19/202011934288238524footballs172103605
15in-store1/2/202011934293997534tennis ball21020510
16in-store1/2/20202374289948447accessories153202660
17in-store1/2/20202374289948447basketballs153302665
18in-store1/2/20202374289948447tennis balls153402670
19in-store1/2/20202374287845101basketballs178403920
20in-store1/2/202022384287845101footballs178503925
21in-store1/21/20208394293142290bowling balls21810905
22delivery1/21/20208394285889023kick balls198104905
23delivery1/21/20208394285889023tennis balls198204910
24delivery1/21/20208394285889023basketballs298304915
25delivery1/21/20208394285889023footballs198404920
26delivery1/21/20208394285889023tennis ball298504925
Sheet1
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,175
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jan 30, 2021
Messages
308
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 14, 2008
Messages
3,175
Office Version
  1. 365
Platform
  1. Windows
Ok, how about this then?

Book2
ABCDEFGHIJKLMNOPQ
1TypeDateOrg #Order IDPrimary CategoryQuantityLine Item TotalLine ProfitTypeDateOrg #Order IDPrimary CategoryQuantityLine Item TotalLine Profit
2in-store1/1/20202374291459722basketballs132901645in-store1/1/20202374291459722basketballs132901645
3in-store1/12/202083994849023basketballs18040in-store1/12/20202374289867784basketballs154302715
4in-store1/12/202083994849023tennis balls19045in-store1/12/20202374289867784footballs154402720
5in-store1/12/20202374289867784basketballs154302715in-store1/12/20202374285932482kick balls197404870
6in-store1/12/20202374289867784footballs154402720in-store1/2/20202374289948447accessories153202660
7in-store1/12/202011934285932482tennis ball197304865in-store1/2/20202374289948447basketballs153302665
8in-store1/12/20202374285932482kick balls197404870in-store1/2/20202374289948447tennis balls153402670
9in-store1/15/20208394292684362softballs121001050in-store1/2/20202374287845101basketballs178403920
10in-store1/18/202022384291833559basketball129801490
11in-store1/18/20208394291833559accessories129901495
12in-store1/18/202011934287441443tennis balls281804090
13in-store1/18/202011934287248219basketballs184704235
14in-store1/19/202011934288238524footballs172103605
15in-store1/2/202011934293997534tennis ball21020510
16in-store1/2/20202374289948447accessories153202660
17in-store1/2/20202374289948447basketballs153302665
18in-store1/2/20202374289948447tennis balls153402670
19in-store1/2/20202374287845101basketballs178403920
20in-store1/2/202022384287845101footballs178503925
21in-store1/21/20208394293142290bowling balls21810905
22delivery1/21/20208394285889023kick balls198104905
23delivery1/21/20208394285889023tennis balls198204910
24delivery1/21/20208394285889023basketballs298304915
25delivery1/21/20208394285889023footballs198404920
26delivery1/21/20208394285889023tennis ball298504925
Sheet1


Power Query:
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

Active Member
Joined
Jan 30, 2021
Messages
308
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,175
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,622
Messages
5,659,926
Members
418,536
Latest member
Tezzies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top