Forum Discussion
Solved
In Search of Advanced Automated Formula to Condense rows while applying Sum
I have several monthly reports I must submit that require very similar processes to be done to the excel data I generate from software I use. I have to print out the pounds of food sold to customers ...
Check out the attached sheet.
I'd be happy to demonstrate this with a more complete copy, if you are able without violating confidentiality, to post a copy of your actual worksheet. In the absence of that, the following steps were all that I needed to take.
- I used one formula that converted your Quantity to just the values.
=VALUE(LEFT([@[QUANTITY SOLD]],FIND(" ",[@[QUANTITY SOLD]])))
- Then I let Excel do the crunch work by means of the Pivot Table, which is one of the most popular (and powerful) tools built into Excel for summarizing the kind of data you have.
If you can post a more extensive example of your raw data, do so either here or on OneDrive or GoogleDrive, pasting a link here in the latter two instances that grants access.
If like this
it could be done by Power Query. Generated script is
let
Source = Excel.CurrentWorkbook(){[Name="report"]}[Content],
removePounds = Table.ReplaceValue(Source,"lbs","",Replacer.ReplaceText,{"QUANTITY SOLD"}),
trimQty = Table.TransformColumns(
removePounds,
{{"QUANTITY SOLD", Text.Trim, type text}}),
declareType = Table.TransformColumnTypes(
trimQty, {
{"PRODUCT", type text},
{"CUSTOMER", type text},
{"QUANTITY SOLD", type number}
}),
#"Grouped Rows" = Table.Group(
declareType, {"PRODUCT", "CUSTOMER"},
{{"Qty", each List.Sum([QUANTITY SOLD]), type nullable number}}),
names = Table.ColumnNames( #"Grouped Rows" ),
blankTable = #table( names, { {null,null,null} } ),
groupAgency = Table.Group(
#"Grouped Rows", {"CUSTOMER"},
{{"Data", each _, type table [PRODUCT=nullable text, CUSTOMER=nullable text, Qty=nullable number]}}),
addBlanks = Table.AddColumn(
groupAgency,
"Custom", each [Data] & blankTable),
keepData = Table.SelectColumns(addBlanks,{"Custom"}),
expandTables = Table.ExpandTableColumn(
keepData,
"Custom",
{"PRODUCT", "CUSTOMER", "Qty"}, {"PRODUCT", "CUSTOMER", "Qty"})
in
expandTables- Good afternoon,
Thank you for the lovely response. I'm trying to use this on my end and I have a follow-up question based off this code. For the Source, you're assigning it the Table 'report', but does that imply that I need to convert my whole excel file to a Table prior to all of this compiling? The data from my software program exports as rows of data, but not pre-assigned in Table form. Thank you.For the PivotTable you may simply select all data and Insert->PivotTable->From table/Range. Not necessary to name it or convert to structured table.
- Yes, I have now figured out how to refine the data in a concise manner via PivotTable. I struggled a bit to utilize Power Query Editor, but I think at this point I might have been attempting to reinvent the wheel with that route. I was able to create a PivotTable and assign Columns "CUSTOMER" and "PRODUCT" in the "Rows" field box, and then set the Values field box to "Sum of QUANTITY SOLD". I truly appreciate the help and support you provided, along with other community members' support; this makes my monthly reporting of data much easier due to a more streamlined process. Have a wonderful day!
With Power Query there are few options. If you query data from the same workbook it shall be named. Data have name if you transfer it into structured table (as in the sample) or you name the range and query but that name. Here is similar but bit different technique.
Perhaps more optimal is to query from another file, when in source file you shall change nothing. In file with such report you may use Data->Get Data->From file->From Excel workbook connector. Even more, you may use From Folder connector. If you have several source reports and would like to combine them into one cleaned report, put all source files into one folder and use that connector. That's important that all files have the same structure. Even with one periodically updated file you may use such connector, with that it's not necessary to care about file name.
For the above entire procedure is more complex, but not dramatically.
