Read Cell Contents as Formula

[-_-]

I have cell contents that look like the following:

Item	Qty
A	2|1|1
B	1|1|1|1
C	0|1|1|1|1

I am trying to multiple the values together as if the "|" was a "*". Text to columns poses some issues as the number of "|" can be up to ~25 and there would need to be null value management.

Is there a way to do a 'find/ replace" for "|" to "*" and read the contents as a formula?

Open to ideas.... 

[Jakinta · Accepted solution]

You can use List.Accumulate as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKqMazRNVWK1YlWcoJyjQxrDMECzkABA6AAGCrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Qty = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Accumulate( List.Transform(Text.Split([Qty],"|"), Number.From) ,1,(s,c)=> s*c), type number)
in
    #"Added Custom"

Result

[-_-]

This worked perfectly! Thank you!