Forum Discussion
Conditionally Insert Row
tl;dr - ForEach columnProduct = foo, Table.InsertRow, Fill Down, Replace columnProduct = foo2 in new row.
Hello All,
I'd like to insert a new row to my table only when columnProduct = foo. The new row will mostly copy the information from that relevant row except columnProduct will be a new value in the new row. I'm not too sure on the multiple steps of M that would do something like this. Any thoughts?
Thanks
You can do it like this:
- Filter your table where Product = "foo"
- replace product name
- append to source
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvPV9JRMgRiE6VYnWilvHyIiBEQm0JFoELGQGymFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Column2 = _t, Column1 = _t]), FilterFooProducts = Table.SelectRows(Source, each ([Product] = "foo")), ReplaceProductName = Table.ReplaceValue(FilterFooProducts,"foo","foo2",Replacer.ReplaceText,{"Product"}), AppendToSource = Source & ReplaceProductName in AppendToSource
4 Replies
M would be the only way to go about inserting a new row, DAX doesn't do that. You would have to create an entirely new table with that row somehow included, perhaps by using something like GENERATESERIES or something. But better in M probably. There is a way to refer to the previous row in M but I can't remember it at the moment. ImkeF will know though.
You can do it like this:
- Filter your table where Product = "foo"
- replace product name
- append to source
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvPV9JRMgRiE6VYnWilvHyIiBEQm0JFoELGQGymFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Column2 = _t, Column1 = _t]), FilterFooProducts = Table.SelectRows(Source, each ([Product] = "foo")), ReplaceProductName = Table.ReplaceValue(FilterFooProducts,"foo","foo2",Replacer.ReplaceText,{"Product"}), AppendToSource = Source & ReplaceProductName in AppendToSourceAh! That's clever! I'll give it a try and let you know if I run into any troubles. Thanks very much.
