cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

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

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Super User III
Super User III

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

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Helper III
Helper III

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.

Highlighted
Helper III
Helper III

Re: Conditionally Insert Row

Worked like a charm!! Thanks very much!

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors