How to insert a row every 3 rows

[Anonymous]

Hello, is there a simple way to insert a row every 3 rows?

My use-case is fairly straightforward, I need a repeating row #question_answer as in the sample:

#question_answer	bregjiewpjgrw
#question	bregjiewpjgrw
#answer	answer1
#question_answer	caherjtykukgkh
#question	caherjtykukgkh
#answer	answer2
#question_answer	abdscefefa
#question	abdscefefa
#answer	answer3
Script to insert here	Script to insert here
#question	dwafegbbg
#answer	answer4

Ideally, as shown, it would contain plain data (the string #question_answer )in the first column, and a reference to the cell below in the second. 

Very grateful for any suggestions

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following steps to meet your requirement.

1. Open the Power Query and we need to add a new customer column,

if [Column 1] = "#question"
then {"New","Origin"}
else {"Origin"}

2. Then we need to expand the columns to new row,

3. Then we can create a replace step and replace with another logical condition.

= Table.ReplaceValue(#"Expanded Temp",each [Column 1],each if [Temp] = "New" then "#question_answer" else  [Column 1],Replacer.ReplaceText,{"Column 1"})

4. At last we can delete the Temp column, and get the result like this,

If you have any questions, please kindly ask here and we will try to resolve it.

All the queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcs7DoAgEATQu6ytjZ/bGIpFllVIUPmEeHtJaEygm8zM2zYYnkQhnpeDEaQnNifl27DPIMayoguZfNlqmGr7Mzse5E18bbJsjy6aG4RShZ00aeyCpQEqoyaWkrv/FYT4AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Temp", each if [Column 1] = "#question"
then {"New","Origin"}
else {"Origin"}),
    #"Expanded Temp" = Table.ExpandListColumn(#"Added Custom", "Temp"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Temp",each [Column 1],each if [Temp] = "New" then "#question_answer" else  [Column 1],Replacer.ReplaceText,{"Column 1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Temp"})
in
    #"Removed Columns"

BTW, pbix as attached.

Best regards,

[Anonymous]

This works perfectly. Thank you for the detailed writeup, it really helps me understand the method.