Forum Discussion
Returning multiple values from a column to a new table
you have to edit Table1 and Table2 step. Delete whole code and replace it with your Table1 reference, i.e. if your Table1 reference is MyTable1 then replace this whole code with = MyTable1 (if you have some special characters or space in table name i.e My Table1, you have to write it like this: #"My Table1"
Do the same for Table2.
Hi EduardoM83,
you should use Merge Queries UI button, but before that you have to do some additional steps. You can check that steps here (in this example both tables created in same query). You will be probably confused - but you can click on every single step and try to understand. BTW. Table1 and Table2 steps (based on JSON) - it is just a raw data which I created via UI buddon Enter Data.
I hope this will help you. (Don't spend time with Table2_ReplaceValue step please. At this moment you will probably don't understand how it works).
let
Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUQouSSzISS1SMASyDZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Product Name" = _t, Quantity = _t]),
Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2MVXSUQouSSzISS1SMEJil4E4hkqxOtFKCkCWAlxKISAxOVuhwtDAAChkhKIAgkEiJqZm5kiGGSMbbKyA32QFU3wmGwLdjGSaIbLJhgQMNgJ51hibgqDU3Pyy1CKI/lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Product Name" = _t, Components = _t, Quantity = _t]),
Table2_ReplacedValue = Table.ReplaceValue(Table2,
null,
null,
(x,y,z)=> if Text.Trim(x) = "" then null else x,
{"Product ID", "Product Name", "Components", "Quantity"}),
Table2_FilledDown = Table.FillDown(Table2_ReplacedValue,{"Product ID"}),
#"Merged Queries" = Table.NestedJoin(Table1, {"Product ID"}, Table2_FilledDown, {"Product ID"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Product Name", "Components", "Quantity"}, {"Table2.Product Name", "Table2.Components", "Table2.Quantity"})
in
#"Expanded Table2"
Hi Dufoq3,
I managed to get this to work which is great, but if I add an additional row in table 1, lets say for Product 2345, it doesnt add that to the edited table. Is that possible?
Thanks again.
you have to edit Table1 and Table2 step. Delete whole code and replace it with your Table1 reference, i.e. if your Table1 reference is MyTable1 then replace this whole code with = MyTable1 (if you have some special characters or space in table name i.e My Table1, you have to write it like this: #"My Table1"
Do the same for Table2.
