Forum Discussion
Replace one value by multiple values
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!
4 Replies
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"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
= 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({_})))
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
