Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
a68tbird
Resolver II
Resolver II

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.