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

Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 28, 2023

Andrew__K 

 

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
    ReorderedColumns

 

Resources