Reply
Member
Posts: 51
Registered: ‎11-06-2015
Accepted Solution

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


Accepted Solutions
Highlighted
Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: Conditionally Insert Row

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

 

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post


All Replies
Super User
Posts: 10,536
Registered: ‎07-11-2015

Re: Conditionally Insert Row

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: Conditionally Insert Row

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

 

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Member
Posts: 51
Registered: ‎11-06-2015

Re: Conditionally Insert Row

Ah! That's clever! I'll give it a try and let you know if I run into any troubles.  Thanks very much.

Member
Posts: 51
Registered: ‎11-06-2015

Re: Conditionally Insert Row

Worked like a charm!! Thanks very much!