Forum Discussion
Formula that changes after finding value in row
Our labelling software generates a CSV of production data daily, its formulated as shown in the table below. The products are listed followed by a summary row of batch 1, batch 2, etc... i.e anything above the row for 'Total of Batch 01' is batch 1, then the list for batch 2 products start after that, followed by a row for 'Total of Batch 02' and so on.
I want to generate a formula that puts the batch number for each product in the row of that product.
It would need to search the B column for 'Total of Batch 01' row and apply 1 to the rows above that in a column, then search for 'Total of Batch 02' and apply 2 to those rows.
I plan on using PowerQuery and transforming the data from each CSV to pull it into another sheet for analysis, I will also need to exclude the 'Total of Batch' rows for this.
Can anyone point me in the right direction of how to do this?
TIA
| Product PPN | Description | Prod Date |
| 10751 | PRODUCT 1 | 17-Jul-23 |
| 10751 | PRODUCT 1 | 25-Jul-23 |
| 14014 | PRODUCT 2 | 26-Oct-23 |
| 15582 | PRODUCT 3 | 26-Oct-23 |
| 85393 | PRODUCT 4 | 26-Oct-23 |
| 87025 | PRODUCT 7 | 25-Oct-23 |
| 87119 | PRODUCT 8 | 20-Oct-23 |
| 87121 | PRODUCT 9 | 25-Oct-23 |
| Total of Batch 01 | ||
| 87025 | PRODUCT 7 | 25-Oct-23 |
| 85393 | PRODUCT 4 | 26-Oct-23 |
| 87119 | PRODUCT 8 | 20-Oct-23 |
| Total of Batch 02 | ||
| 87025 | PRODUCT 7 | 25-Oct-23 |
| 87119 | PRODUCT 8 | 20-Oct-23 |
| 87121 | PRODUCT 9 | 25-Oct-23 |
| 87712 | PRODUCT 10 | 25-Oct-23 |
| 87306 | PRODUCT 12 | 25-Oct-23 |
| Total of Batch 03 |
I think the key is to create a conditional column and fill the Batch # upwards.
let Source = Excel.CurrentWorkbook(){[Name="Production"]}[Content], TypeDate = Table.TransformColumnTypes(Source,{{"Product PPN", Int64.Type}, {"Description ", type text}, {" Prod Date ", type date}}), AddedConditionalColumn = Table.AddColumn(TypeDate, "Custom", each if [#"Product PPN"] = null then [#"Description "] else null), FilledUp = Table.FillUp(AddedConditionalColumn,{"Custom"}), BatchNumbered = Table.ReplaceValue(FilledUp,"Total of ","",Replacer.ReplaceText,{"Custom"}), FilteredNullRows = Table.SelectRows(BatchNumbered, each [#"Product PPN"] <> null and [#"Product PPN"] <> ""), Restuctured = Table.ReorderColumns(FilteredNullRows,{"Custom", "Product PPN", "Description ", " Prod Date "}) in Restuctured
4 Replies
Variant attached:
let Source = Excel.CurrentWorkbook(){[Name="CsvData"]}[Content], ChangedTypes = Table.TransformColumnTypes(Source, {{"Product PPN", Int64.Type}, {"Description", type text}, {"Prod Date", type date}} ), AddedBatchNum = Table.AddColumn(ChangedTypes, "Batch #", each if [Product PPN] is null then Text.AfterDelimiter([Description], " ", 1) else null, type text ), FilledUp = Table.FillUp(AddedBatchNum,{"Batch #"}), FilteredNullProduct = Table.SelectRows(FilledUp, each ([Product PPN] <> null)), ReorderedColumns = Table.ReorderColumns(FilteredNullProduct, {"Batch #", "Product PPN", "Description", "Prod Date"} ) in ReorderedColumnsI think the key is to create a conditional column and fill the Batch # upwards.
let Source = Excel.CurrentWorkbook(){[Name="Production"]}[Content], TypeDate = Table.TransformColumnTypes(Source,{{"Product PPN", Int64.Type}, {"Description ", type text}, {" Prod Date ", type date}}), AddedConditionalColumn = Table.AddColumn(TypeDate, "Custom", each if [#"Product PPN"] = null then [#"Description "] else null), FilledUp = Table.FillUp(AddedConditionalColumn,{"Custom"}), BatchNumbered = Table.ReplaceValue(FilledUp,"Total of ","",Replacer.ReplaceText,{"Custom"}), FilteredNullRows = Table.SelectRows(BatchNumbered, each [#"Product PPN"] <> null and [#"Product PPN"] <> ""), Restuctured = Table.ReorderColumns(FilteredNullRows,{"Custom", "Product PPN", "Description ", " Prod Date "}) in Restuctured- Thanks Peter! Looks like that will work quite well!
Out of curiosity, did you structure that code yourself or is it generated from applying settings in the ribbon?I tend to prioritise working with dynamic arrays on the worksheet and only work with PowerQuery when there is a need to do so. As a result I still rely heavily upon the ribbon. I do, however, read and modify the code it generates as required. Some of that is no more than renaming steps or removing unnecessary changes of data type. Here, I notice that I did not change the name 'custom' at the point at which it was created. 'BatchNumber' might have been more appropriate since that default name found its way through to the worksheet table as a column header.
