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 brea...
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