Forum Discussion

Andrew__K's avatar
Andrew__K
Copper Contributor
Oct 27, 2023
Solved

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...
excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Oct 27, 2023

    Andrew__K 

    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

Andrew__K's avatar
Andrew__K
Copper Contributor
Oct 27, 2023
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?
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 28, 2023

Andrew__K 

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. 

Resources