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!

 

[ExcelAtEverything]

Ok. Is that what I "should" do, or is there some other way you had in mind that is better? Sorry, I'm a newb.

 

[lrobbo314]

I would just look at the steps I posted and apply them at the end of the steps you had in in photo above. But, this way would be easier if you're not super familiar with Power Query.

 

[ExcelAtEverything]

I would just look at the steps I posted and apply them at the end of the steps you had in in photo above. But, this way would be easier if you're not super familiar with Power Query.

Assuming that this means each line of your code is a new step to create, I'm going to try it that way.

 

[ExcelAtEverything]

When trying to enter the M code into the Query as steps inserted at the end of "Applied Steps", what then do I do with the lines that say "Let", and "in"? Also, what about the last part that just says "ROC"? Is that the entire step?

 

[ExcelAtEverything]

I can tell that you seem pretty advanced with PQ. But I think your explanation of how to implement it assumes I know more than I do. I can create the query, change types, and do simple common sense changes. But I am LOST when it comes to the code I think that might have been the second time I ever even opened the advanced editor in PQ. I know virtually nothing of how to use it. If Power Query were a 1000 page book, I'd be on maybe page 30. I really appreciate your help. I'd just like to be able to try it out. ?

 

[ExcelAtEverything]

Ok Irobbo, with some much needed assistance I was able to get it running! Only problem is that it doesn't seem to work. I'm not sure how to read it to see what it's doing, but in my pic you will see the bottom row (which is Org #1193). That store grand opened on 7/9/20 . If the code worked then it would show zeros until 8/1/20.. Coul you take another look? Below that I pasted in the code as it is in my machine now.

let
    Source = Folder.Files("C:\Users\ibelieveinme\Desktop\DO NOT DELETE or MOVE - Excel Data Files For MOR Report\LY Orders Report"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (19)", each #"Transform File (19)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (19)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (19)", Table.ColumnNames(#"Transform File (19)"(#"Sample File (19)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Order ID", Int64.Type}, {"Date", type datetime}, {"Time", type datetime}, {"Status", type text}, {"Type", type text}, {"Medical", type text}, {"Customer Name", type text}, {"Street", type any}, {"City", type any}, {"Postal Code", type any}, {"Product", type text}, {"Primary Category", type text}, {"Option", type any}, {"Quantity", Int64.Type}, {"On Sale", type text}, {"Unit Price", type number}, {"Unit Price w/o Taxes", type number}, {"Line Item Subtotal", type number}, {"Line Item Discounts Total", type number}, {"Line Item Discounts", type text}, {"Line Item Total", type number}, {"Line Item Total w/o Taxes", type number}, {"Line Item Adjusted Total", type number}, {"Order Subtotal", type number}, {"Order Credits", Int64.Type}, {"Order Adjustments", Int64.Type}, {"Order Discounts", type number}, {"Order Taxes", type number}, {"Order Loyalty", Int64.Type}, {"Order Grand Total", type number}, {"Order Total w/o Tax", type number}, {"Payment Type", type text}, {"Payment Type Fee", Int64.Type}, {"Source", type text}, {"Placed By", type text}, {"Station Name", type text}, {"Cost per Unit", type number}, {"Profit per Unit", type number}, {"Unit Margin %", type number}, {"Profit per Unit After Line Item Discounts", type number}, {"Unit Margin % After Line Item Discounts", type number}, {"Line Cost", type number}, {"Line Profit", type number}, {"Packed At", type any}, {"Packed By", type text}, {"Fulfilled At", type datetimezone}, {"Fulfilled By", type text}, {"Option ID", Int64.Type}, {"Package / Metrc Tag", type text}, {"Worker", type any}, {"Brand", type text}, {"Package Line Costs", type number}, {"Package Line CA Excise", type number}, {"Use Tax", Int64.Type}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}}),
    Custom1 = Table.AddColumn(#"Changed Type", "Custom", each if not Text.EndsWith([Primary Category], "s") then Text.Combine({[Primary Category], "s"})
 else [Primary Category]),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"Primary Category"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Primary Category"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2",{"Source.Name", "Order ID", "Date", "Time", "Status", "Type", "Medical", "Customer Name", "Street", "City", "Postal Code", "Product", "Primary Category", "Option", "Quantity", "On Sale", "Unit Price", "Unit Price w/o Taxes", "Line Item Subtotal", "Line Item Discounts Total", "Line Item Discounts", "Line Item Total", "Line Item Total w/o Taxes", "Line Item Adjusted Total", "Order Subtotal", "Order Credits", "Order Adjustments", "Order Discounts", "Order Taxes", "Order Loyalty", "Order Grand Total", "Order Total w/o Tax", "Payment Type", "Payment Type Fee", "Source", "Placed By", "Station Name", "Cost per Unit", "Profit per Unit", "Unit Margin %", "Profit per Unit After Line Item Discounts", "Unit Margin % After Line Item Discounts", "Line Cost", "Line Profit", "Packed At", "Packed By", "Fulfilled At", "Fulfilled By", "Option ID", "Package / Metrc Tag", "Worker", "Brand", "Package Line Costs", "Package Line CA Excise", "Use Tax", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Source.Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.1", "Source.Name.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Source.Name.1", type text}, {"Source.Name.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Source.Name.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.2.1", "Source.Name.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Source.Name.2.1", Int64.Type}, {"Source.Name.2.2", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Type", "Date", "Source.Name.2.1", "Order ID", "Primary Category", "Quantity", "Line Item Total", "Line Profit"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Source.Name.2.1", "Org #"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type3",null,0,Replacer.ReplaceValue,{"Line Profit"})
in
    #"Replaced Value"

 

[ExcelAtEverything]

I am assuming that this will be working very soon, which is AMAZING! However, I was wondering if in addition to getting this first one working, you might be able to do the same thing with my other 'last year' query, which is for discounting. Basically very similar, but with a much smaller 5-column query. If this is too much to ask, then I don't want to look a gift horse in the mouth. I can figure it out once we have a working copy, but it'll take me some time as coding isn't my strong suit by far. But if you're cool with it then here it is. (And thanks again!)

Basically again, I need the exact same thing to happen with this query. Just a slightly different query to do it to. (Sorry, but I had to uninstall XL2BB as it was causing constant crashes to my Excel again. But here is a screenshot, followed by the current query as it is now.

let
    Source = Folder.Files("C:\Users\ibelieveinme\Desktop\DO NOT DELETE or MOVE - Excel Data Files For MOR Report\LY Orders Report"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (21)", each #"Transform File (21)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (21)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (21)", Table.ColumnNames(#"Transform File (21)"(#"Sample File (21)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"ID", Int64.Type}, {"Date", type datetime}, {"Time", type datetime}, {"Status", type text}, {"Type", type text}, {"Medical", type text}, {"Tax Exempt", type text}, {"Customer Name", type text}, {"Member ID", type text}, {"Street", type any}, {"City", type any}, {"Postal Code", type any}, {"Line Items", type text}, {"Discounts Items", type text}, {"Adjustment Items", type any}, {"Subtotal", type number}, {"Credits", Int64.Type}, {"Adjustments", Int64.Type}, {"Order Discounts", type number}, {"LineItem Discounts Total", type number}, {"Loyalty", Int64.Type}, {"Taxes", type number}, {"Grand Total", type number}, {"Total w/o Tax", type number}, {"Payment Type", type text}, {"Payment Type Fee", Int64.Type}, {"Source", type text}, {"Placed By", type text}, {"Station Name", type text}, {"Onfleet ID", type any}, {"Onfleet Short ID", type any}, {"Onfleet Started At", type any}, {"Onfleet Departed At", type any}, {"Onfleet Arrived At", type any}, {"Onfleet Completed At", type any}, {"Onfleet Failed At", type any}, {"Worker", type any}, {"Order to Delivery Time", type any}, {"Onfleet Delivery Time", type any}, {"Weekday", Int64.Type}, {"Time of Day", type datetime}, {"Total Cost of Goods", type number}, {"Grand Total Profit", type number}, {"Total w/o Tax Profit", type number}, {"Packed At", type any}, {"Packed By", type text}, {"Fulfilled At", type datetimezone}, {"Fulfilled By", type text}, {"State sales-6-false", type number}, {"City benefits-6-false", type number}, {"State excise-15-false", type number}, {"State sales tax-7.88-false", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Source.Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.1", "Source.Name.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Source.Name.1", type text}, {"Source.Name.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Source.Name.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.2.1", "Source.Name.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Source.Name.2.1", Int64.Type}, {"Source.Name.2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Source.Name.2.1", "Org #"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Org #", "ID", "Date", "Type", "Credits", "Adjustments", "Order Discounts", "LineItem Discounts Total"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}}),
    #"Inserted Sum" = Table.AddColumn(#"Changed Type3", "Addition", each List.Sum({[Order Discounts], [LineItem Discounts Total]}), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Sum",{"Credits", "Adjustments", "Order Discounts", "LineItem Discounts Total"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Addition", "Total Discount"}})
in
    #"Renamed Columns2"

 

[ExcelAtEverything]

Irobbo314, I feel like I should apologize to you. I was so obsessed with getting this issue resolved that instead of taking what you sent me and doing some research of my own to learn what I need to learn in order to be able to actually use youre code, I tried to put all the work on you as well as on another person on this site. If I'm going to ask people like you to help, then I have to be willing to learn what I need to learn in order to implement it. I took today and read as much as I could on Power Query and how things are setup. While I still have a long way to go, I understand a lot better now how things in the query are setup, proper syntax, etc.

 

[ExcelAtEverything]

I plugged your code into my query & while it looks correct to me, each time I run it it locks my computer up (my CPU usage maxes out & I just see the dots going across the query screen as is typical when the query is being updated). I noticed that you didn't have #" and " surrounding your variable names. Is that supposed to be like that, or should I add that hashtag & pair of quotes around each one? I wasn't sure, so I tried it both ways. Both delivered the same result in locking my computer up for a long enough time that I had to force quit Excel to stop it (the longest of which was just over for an hour). While this query is affecting several rather large spreadsheets and has about 10 pivot tables accessing it, I can't imagine it would take that long to update it after I enter this and hit enter. When I "Refresh All" my connections and queries, it only takes about 5 minutes. Any idea what the problem is here?

Since my query was functioning prior to adding your code, one would have to assume that it's either something in your code, or in the way that I implemented it which is causing this. Your code was pasted in near the bottom. It begins at:
Type = Table.TransformColumnTypes(#"Replaced Value"

let
    Source = Folder.Files("C:\Users\ibelieveinme\Desktop\DO NOT DELETE or MOVE - Excel Data Files For MOR Report\LY Orders Report"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (19)", each #"Transform File (19)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (19)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (19)", Table.ColumnNames(#"Transform File (19)"(#"Sample File (19)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Order ID", Int64.Type}, {"Date", type datetime}, {"Time", type datetime}, {"Status", type text}, {"Type", type text}, {"Medical", type text}, {"Customer Name", type text}, {"Street", type any}, {"City", type any}, {"Postal Code", type any}, {"Product", type text}, {"Primary Category", type text}, {"Option", type any}, {"Quantity", Int64.Type}, {"On Sale", type text}, {"Unit Price", type number}, {"Unit Price w/o Taxes", type number}, {"Line Item Subtotal", type number}, {"Line Item Discounts Total", type number}, {"Line Item Discounts", type text}, {"Line Item Total", type number}, {"Line Item Total w/o Taxes", type number}, {"Line Item Adjusted Total", type number}, {"Order Subtotal", type number}, {"Order Credits", Int64.Type}, {"Order Adjustments", Int64.Type}, {"Order Discounts", type number}, {"Order Taxes", type number}, {"Order Loyalty", Int64.Type}, {"Order Grand Total", type number}, {"Order Total w/o Tax", type number}, {"Payment Type", type text}, {"Payment Type Fee", Int64.Type}, {"Source", type text}, {"Placed By", type text}, {"Station Name", type text}, {"Cost per Unit", type number}, {"Profit per Unit", type number}, {"Unit Margin %", type number}, {"Profit per Unit After Line Item Discounts", type number}, {"Unit Margin % After Line Item Discounts", type number}, {"Line Cost", type number}, {"Line Profit", type number}, {"Packed At", type any}, {"Packed By", type text}, {"Fulfilled At", type datetimezone}, {"Fulfilled By", type text}, {"Option ID", Int64.Type}, {"Package / Metrc Tag", type text}, {"Worker", type any}, {"Brand", type text}, {"Package Line Costs", type number}, {"Package Line CA Excise", type number}, {"Use Tax", Int64.Type}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}}),
    Custom1 = Table.AddColumn(#"Changed Type", "Custom", each if not Text.EndsWith([Primary Category], "s") then Text.Combine({[Primary Category], "s"})
 else [Primary Category]),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"Primary Category"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Primary Category"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2",{"Source.Name", "Order ID", "Date", "Time", "Status", "Type", "Medical", "Customer Name", "Street", "City", "Postal Code", "Product", "Primary Category", "Option", "Quantity", "On Sale", "Unit Price", "Unit Price w/o Taxes", "Line Item Subtotal", "Line Item Discounts Total", "Line Item Discounts", "Line Item Total", "Line Item Total w/o Taxes", "Line Item Adjusted Total", "Order Subtotal", "Order Credits", "Order Adjustments", "Order Discounts", "Order Taxes", "Order Loyalty", "Order Grand Total", "Order Total w/o Tax", "Payment Type", "Payment Type Fee", "Source", "Placed By", "Station Name", "Cost per Unit", "Profit per Unit", "Unit Margin %", "Profit per Unit After Line Item Discounts", "Unit Margin % After Line Item Discounts", "Line Cost", "Line Profit", "Packed At", "Packed By", "Fulfilled At", "Fulfilled By", "Option ID", "Package / Metrc Tag", "Worker", "Brand", "Package Line Costs", "Package Line CA Excise", "Use Tax", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Source.Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.1", "Source.Name.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Source.Name.1", type text}, {"Source.Name.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Source.Name.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.2.1", "Source.Name.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Source.Name.2.1", Int64.Type}, {"Source.Name.2.2", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Type", "Date", "Source.Name.2.1", "Order ID", "Primary Category", "Quantity", "Line Item Total", "Line Profit"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Source.Name.2.1", "Org #"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type3",null,0,Replacer.ReplaceValue,{"Line Profit"}),
    Type = Table.TransformColumnTypes(#"Replaced Value",{{"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