ExcelAtEverything
Active Member
- Joined
- Jan 30, 2021
- Messages
- 351
- Office Version
- 2019
- Platform
- Windows
Hello,
In the screenshot. you see a tiny beginning section of a long list of sales which ultimately leads up to yesterday's date. Each row represents an individual product which was sold, which sales ticket (Order ID) it was sold on, the date, quantity sold, and a lot more.
I need for the query (code shown below) to check the "Date" column of each row. For each row in which the "Date" shown is prior to 2/01/21, I need to replace the value show in the "Line Profit" column with the current value times 1.5. In other words since all of the items in the screenshot below occur prior to 2/01/21 (as evidenced by their respective 'Date") , then each value in the "Line Profit" column would be replaced with a multiple of 1.5. So "37.5" would become "56.25", "7" would become "10.5", and so on. Otherwise if the date is on or after 2/1/21, then the number in the "Line Profit" column would remain unchanged,
Thanks!
In the screenshot. you see a tiny beginning section of a long list of sales which ultimately leads up to yesterday's date. Each row represents an individual product which was sold, which sales ticket (Order ID) it was sold on, the date, quantity sold, and a lot more.
I need for the query (code shown below) to check the "Date" column of each row. For each row in which the "Date" shown is prior to 2/01/21, I need to replace the value show in the "Line Profit" column with the current value times 1.5. In other words since all of the items in the screenshot below occur prior to 2/01/21 (as evidenced by their respective 'Date") , then each value in the "Line Profit" column would be replaced with a multiple of 1.5. So "37.5" would become "56.25", "7" would become "10.5", and so on. Otherwise if the date is on or after 2/1/21, then the number in the "Line Profit" column would remain unchanged,
Thanks!
Power Query:
let
Source = Folder.Files("C:\Users\itsme\Desktop\DO NOT DELETE or MOVE - Excel Data Files For MOR Report\Monthly Orders Reports"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (11)", each #"Transform File (11)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (11)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (11)", Table.ColumnNames(#"Transform File (11)"(#"Sample File (11)"))),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Table Column1",{"Source.Name", "Order ID", "Date", "Type", "Quantity", "Unit Price w/o Taxes", "Line Item Subtotal", "Line Item Total", "Line Item Adjusted Total", "Order Subtotal", "Order Discounts", "Order Total w/o Tax", "Cost per Unit", "Profit per Unit", "Line Cost", "Line Profit", "Primary Category"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Source.Name", type text}, {"Order ID", Int64.Type}, {"Date", type datetime}, {"Type", type text}, {"Quantity", Int64.Type}, {"Unit Price w/o Taxes", Int64.Type}, {"Line Item Subtotal", Int64.Type}, {"Line Item Total", Int64.Type}, {"Line Item Adjusted Total", type number}, {"Order Subtotal", Int64.Type}, {"Order Discounts", type number}, {"Order Total w/o Tax", type number}, {"Cost per Unit", type number}, {"Profit per Unit", type number}, {"Line Cost", type number}, {"Line Profit", type number}}),
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"}}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Renamed Columns2", "Source.Name", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Source.Name.1", "Source.Name.2", "Source.Name.3", "Source.Name.4", "Source.Name.5", "Source.Name.6", "Source.Name.7", "Source.Name.8"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Character Transition", "Source.Name.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Source.Name.1.1", "Source.Name.1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Source.Name.1.1", type text}, {"Source.Name.1.2", Int64.Type}, {"Source.Name.2", type text}, {"Source.Name.3", Int64.Type}, {"Source.Name.4", Int64.Type}, {"Source.Name.5", type text}, {"Source.Name.6", Int64.Type}, {"Source.Name.7", Int64.Type}, {"Source.Name.8", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Source.Name.1.1", "Source.Name.8", "Source.Name.2", "Source.Name.3", "Source.Name.4", "Source.Name.5", "Source.Name.6", "Source.Name.7"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Date", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Source.Name.1.2", "Org #"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [#"Org #"] <> null and [#"Org #"] <> ""),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Org #", "Order ID", "Date", "Type", "Quantity", "Line Profit", "Unit Price w/o Taxes", "Line Item Subtotal", "Line Item Total", "Line Item Adjusted Total", "Order Subtotal", "Order Discounts", "Order Total w/o Tax", "Cost per Unit", "Profit per Unit", "Line Cost", "Primary Category"})
in
#"Reordered Columns"
