Forum Discussion
Edit cell in duplicated row
Hi,
I'm trying to create a table that duplicates certain rows and then clears the contents of a cell in the original row (In this case B2).
I have managed to duplicate the rows using the List.Repeat function.
How do I then remove the "2" in cell B2 using Power Query editor?
Original Table
Desired State: Row 2 duplicated using List.Repeat & "2" cleared from B2
I would just make a duplicate of your original non repeating table, filter the duplicate table to just be the rows that you want repeated, whatever criteria that might be. Once filtered, remove the duplicate column, and add a custom column from the GUI named "duplicate", and make the formula just:
= null
Now, you can use the Append Query button, which is Table.Combine({Table1, Table2}).
Then just sort by Product, then by duplicate-descending.
That's it!
--Nate
5 Replies
You can use the Replace function, it is on the Transform menu.
edhans this will replace the "2" in cell B3 also, I only want to clear the contents of cell B2.
Well, you can do it this way:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEyUorViVZySswDQiDXQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), Custom1 = Table.SelectRows(Source, each [Column1] = "Apple"), #"Replaced Value" = Table.ReplaceValue(Custom1,"2","",Replacer.ReplaceText,{"Column2"}), #"Appended Query" = Table.Combine({#"Replaced Value", Source}) in #"Appended Query"But you aren't being clear on how or why Apple is being duplicated.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
I would just make a duplicate of your original non repeating table, filter the duplicate table to just be the rows that you want repeated, whatever criteria that might be. Once filtered, remove the duplicate column, and add a custom column from the GUI named "duplicate", and make the formula just:
= null
Now, you can use the Append Query button, which is Table.Combine({Table1, Table2}).
Then just sort by Product, then by duplicate-descending.
That's it!
--Nate
thanks Anonymous that's worked a treat
