Replace one value by multiple values

[mridz]

Hi everyone!

I have the following table (simplification):

Product	Ingredients	weight %
A	a	0.5
A	b	0.5
a	m	0.4
a	n	0.6
m	x	0.9
m	z	0.1

I need to break down the A formula in order to get this:

Product	Ingredients	weight %
A	b	0.5
A	n	0.5*0.6
A	x	0.5*0.4*0.9
A	z	0.5*0.4*0.1

How can I do this? My dataset is extensive.

Thank you!

[wdx223_Daniel]

= let tbl=Table.Buffer(Source),fx=(t)=>let a=tbl{[Ingredients=t{0}]}? in if a=null then t else @Fx({a[Product],t{1}? ??a[Ingredients],Text.Combine({a[#"weight %"],t{2}?},"*")}) in #table(Table.ColumnNames(Source),List.Transform(List.RemoveItems(Source[Ingredients],Source[Product]),each fx({_})))

[AlexisOlson]

Classic @wdx223_Daniel! Brilliant solution but takes effort to understand.

I think I've deciphered it and this is my annotated translation of it that may help future readers follow along too:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoEYgM9U6VYHQg/CYkPkssF803g/Dww3wzMB8lVgPmWcH4VmG+oFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Ingredients = _t, Weight = _t]),
  tbl = Table.Buffer(Source),
  /*Define recursive lookup function.*/
  fx =
    (L as list) => // {lookup, (optional) ingredient, (optional) weight}
    let
      lookup     = L{0},  // 1st item in list
      ingredient = L{1}?, // 2nd item in list or else null
      weight     = L{2}?, // 3rd item in list or else null
      /*Lookup ingredient row in tbl or return null*/
      row = tbl{[Ingredients = lookup]}?,
      recursive_step =
        if row = null
        then L // End recursion
        else   // Recursively call fx on new values
          @fx(
            {
              row[Product],                            // new lookup
              ingredient ?? row[Ingredients],          // new ingredient
            /*List.Product({row[Weight], weight}),     // multiply weights*/
              Text.Combine({row[Weight], weight}, "*") // concatenate weights
            }
          )
    in
      recursive_step,
  /*Ingredients not appearing in the [Product] column*/
  leaf_ingredients = List.RemoveItems(tbl[Ingredients], tbl[Product]),
  /*Apply recursive lookup function to each leaf ingredient.
    Each ingredient becomes {Product, ingredient, weights}.*/
  tbl_rows = List.Transform(leaf_ingredients, each fx({_})),
  col_names = Table.ColumnNames(tbl),
  /*Construct table from column names and list of rows*/
  result = #table(col_names, tbl_rows)
in
  result

[AlexisOlson]

This will likely require a form of recursion.

I might get back to this later but if not, here's some tinkering I've done so far that basically solves it but without making it dynamic.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoEYgM9U6VYHQg/CYkPkssF803g/Dww3wzMB8lVgPmWcH4VmG+oFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Ingredients = _t, Weight = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"Ingredients"}, Source, {"Product"}, "Source", JoinKind.LeftOuter),
    #"Expanded Source" = Table.ExpandTableColumn(#"Merged Queries", "Source", {"Ingredients", "Weight"}, {"Ingredients.2", "Weight.2"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Source", {"Ingredients.2"}, #"Expanded Source", {"Ingredients"}, "Expanded Source", JoinKind.LeftOuter),
    #"Expanded Expanded Source" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Source", {"Ingredients.2", "Weight.2"}, {"Ingredients.3", "Weight.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Expanded Source",{{"Ingredients", "Ingredients.1"}, {"Weight", "Weight.1"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Product] = "A")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Ingredient", each List.Last(List.RemoveNulls({[Ingredients.1],[Ingredients.2],[Ingredients.3]})), type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Weight", each Text.Combine(List.RemoveNulls({[Weight.1],[Weight.2],[Weight.3]}), "*")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Product", "Ingredient", "Weight"})
in
    #"Removed Other Columns"

[mridz]

First of all thank you a lot!

I think that is similar to what I have tried, but at the end I had to do too many merges (6!) because there where more ingredients with ingredients than I thought..

let
    Source = Table.NestedJoin(#"tracked products", {"Product"}, oracle_appended, {"PRODUCT"}, "tracked products", JoinKind.LeftOuter),
    #"Expanded tracked products" = Table.ExpandTableColumn(Source, "tracked products", {"PRODUCT", "ITEM_NUMBER", "PLANT", "ITEM_DESCRIPTION", "QTY", "PROD_QTY", "weight %", "F_VERS", "CREATION_DATE", "LAST_UPDATE_DATE", "PTYPE_DESC", "ITYPE_DESC"}, {"PRODUCT.1", "ITEM_NUMBER", "PLANT", "ITEM_DESCRIPTION", "QTY", "PROD_QTY", "weight %", "F_VERS", "CREATION_DATE", "LAST_UPDATE_DATE", "PTYPE_DESC", "ITYPE_DESC"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded tracked products",{{"PRODUCT.1", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Product"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([PRODUCT.1] <> null)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"ITEM_NUMBER", "PLANT"}, oracle_appended, {"PRODUCT", "PLANT"}, "oracle_appended", JoinKind.LeftOuter),
    #"Expanded oracle_appended" = Table.ExpandTableColumn(#"Merged Queries", "oracle_appended", {"PRODUCT", "ITEM_NUMBER", "PLANT", "ITEM_DESCRIPTION", "QTY", "PROD_QTY", "weight %", "F_VERS", "CREATION_DATE", "LAST_UPDATE_DATE", "PTYPE_DESC", "ITYPE_DESC"}, {"2.PRODUCT", "2.ITEM_NUMBER", "2.PLANT", "2.ITEM_DESCRIPTION", "2.QTY", "2.PROD_QTY", "2.weight %", "2.F_VERS", "2.CREATION_DATE", "2.LAST_UPDATE_DATE", "2.PTYPE_DESC", "2.ITYPE_DESC"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded oracle_appended", {"2.ITEM_NUMBER", "2.PLANT"}, oracle_appended, {"PRODUCT", "PLANT"}, "oracle_appended", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
    #"Expanded oracle_appended1" = Table.ExpandTableColumn(#"Merged Queries1", "oracle_appended", {"PRODUCT", "ITEM_NUMBER", "PLANT", "ITEM_DESCRIPTION", "QTY", "PROD_QTY", "weight %", "F_VERS", "CREATION_DATE", "LAST_UPDATE_DATE", "PTYPE_DESC", "ITYPE_DESC"}, {"3.PRODUCT", "3.ITEM_NUMBER", "3.PLANT", "3.ITEM_DESCRIPTION", "3.QTY", "3.PROD_QTY", "3.weight %", "3.F_VERS", "3.CREATION_DATE", "3.LAST_UPDATE_DATE", "3.PTYPE_DESC", "3.ITYPE_DESC"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded oracle_appended1", {"3.ITEM_NUMBER", "3.PLANT"}, oracle_appended, {"PRODUCT", "PLANT"}, "oracle_appended", JoinKind.LeftOuter),
    #"Expanded oracle_appended2" = Table.ExpandTableColumn(#"Merged Queries2", "oracle_appended", {"PRODUCT", "ITEM_NUMBER", "PLANT", "ITEM_DESCRIPTION", "QTY", "PROD_QTY", "weight %", "F_VERS", "CREATION_DATE", "LAST_UPDATE_DATE", "PTYPE_DESC", "ITYPE_DESC"}, {"4.PRODUCT", "4.ITEM_NUMBER", "4.PLANT", "4.ITEM_DESCRIPTION", "4.QTY", "4.PROD_QTY", "4.weight %", "4.F_VERS", "4.CREATION_DATE", "4.LAST_UPDATE_DATE", "4.PTYPE_DESC", "4.ITYPE_DESC"}),
    #"Merged Queries3" = Table.NestedJoin(#"Expanded oracle_appended2", {"4.ITEM_NUMBER", "4.PLANT"}, oracle_appended, {"PRODUCT", "PLANT"}, "oracle_appended", JoinKind.LeftOuter),
    #"Expanded oracle_appended3" = Table.ExpandTableColumn(#"Merged Queries3", "oracle_appended", {"PRODUCT", "ITEM_NUMBER", "PLANT", "ITEM_DESCRIPTION", "QTY", "PROD_QTY", "weight %", "F_VERS", "CREATION_DATE", "LAST_UPDATE_DATE", "PTYPE_DESC", "ITYPE_DESC"}, {"5.PRODUCT", "5.ITEM_NUMBER", "5.PLANT", "5.ITEM_DESCRIPTION", "5.QTY", "5.PROD_QTY", "5.weight %", "5.F_VERS", "5.CREATION_DATE", "5.LAST_UPDATE_DATE", "5.PTYPE_DESC", "5.ITYPE_DESC"}),
    #"Merged Queries4" = Table.NestedJoin(#"Expanded oracle_appended3", {"5.ITEM_NUMBER", "5.PLANT"}, oracle_appended, {"PRODUCT", "PLANT"}, "oracle_appended", JoinKind.LeftOuter),
    #"Expanded oracle_appended4" = Table.ExpandTableColumn(#"Merged Queries4", "oracle_appended", {"PRODUCT", "ITEM_NUMBER", "PLANT", "ITEM_DESCRIPTION", "QTY", "PROD_QTY", "weight %", "F_VERS", "CREATION_DATE", "LAST_UPDATE_DATE", "PTYPE_DESC", "ITYPE_DESC"}, {"6.PRODUCT", "6.ITEM_NUMBER", "6.PLANT", "6.ITEM_DESCRIPTION", "6.QTY", "6.PROD_QTY", "6.weight %", "6.F_VERS", "6.CREATION_DATE", "6.LAST_UPDATE_DATE", "6.PTYPE_DESC", "6.ITYPE_DESC"}),
    #"Merged Queries5" = Table.NestedJoin(#"Expanded oracle_appended4", {"6.ITEM_NUMBER", "6.PLANT"}, oracle_appended, {"PRODUCT", "PLANT"}, "oracle_appended", JoinKind.LeftOuter),
    #"Expanded oracle_appended5" = Table.ExpandTableColumn(#"Merged Queries5", "oracle_appended", {"PRODUCT", "ITEM_NUMBER", "PLANT", "ITEM_DESCRIPTION", "QTY", "PROD_QTY", "weight %", "F_VERS", "CREATION_DATE", "LAST_UPDATE_DATE", "PTYPE_DESC", "ITYPE_DESC"}, {"7.PRODUCT", "7.ITEM_NUMBER", "7.PLANT", "7.ITEM_DESCRIPTION", "7.QTY", "7.PROD_QTY", "7.weight %", "7.F_VERS", "7.CREATION_DATE", "7.LAST_UPDATE_DATE", "7.PTYPE_DESC", "7.ITYPE_DESC"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded oracle_appended5")
in
    #"Removed Duplicates"

And still, then I will have to add a step that consolidates everything and gets me the first not null value for all columns, starting from the column on the far right, because I get something like this from the previous code:

Product	Ingredient	Weight	Product.1	Ingredient.1	Weight.1	...
A	a	0.5	a	m	0.4	...
A	a	0.5	a	n	0.6	...
A	b	0.5	null	null	null	..

... times 6.

So I will try the dynamic solution. Any further ideas please do share! Thanks again