I have cell contents that look like the following:
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....
Go to Solution.
You can use List.Accumulate as below.
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)
View solution in original post
This worked perfectly! Thank you!
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.