cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
a68tbird
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

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
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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

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

Worked like a charm!! Thanks very much!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!