insert row with specified values if condition met

[barry62]

I'd like to have power query insert a row above every row that contains the value "Question" in the first column.  The new row needs to contain the value "score" in the first column and "1" in the third column.

becomes

[SundarRaj]

Hi @barry62 , here's a solution you could possibly look at. I'll attach the images of the output, source table and the M code used. Thanks!

[barry62]

Thank you so much.

[AmiraBedh · Accepted solution]

You need to add an index column for sorting later and create a table with the Score rows to insert.

Append the score rows to the original table and the sort back into original order.

Finally, remove the Index column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVRBbtwwDPyKsKcWSAI0yb3YLnookEuBAj0EOdA2vWIqi4akzWZv/Ua/15d0aHmTdOMc2pMhiRwOOUPf3q6+7jgX0bg6W3330nqnvSueXa8h6F7i1uVChQeOJTvJrqQdH2NaTaMmvCI6DXbb7LJEzvkj4FZ3Z7erdcx7Tu4Dzt+Q0SilzgI7SdwWTdllBvCoQdqDwfwFDF6OYudoHFWMgD1q30vLKV8A8/FlkUtcbDzFLYNprLH7iEgvo9XMRdsfzlN25CJvAf/AjvseRJzGuXAsEndSDqc9gsrFSVNXOK/joeJ6DR3uBjogZRikvGqkKOpagURtOcW6xvlLsQEz5TqIF7DZkoN2W8Y4ZspGfkxaIJ+jLUnEd6BI20mrJ/z/1Zdc1jBVGJOwKYUpLsm6Xgp099ALYHt16GTQxFCkkwfpdhQw/4xBnFdxTidxOVvlBNW4jQjXSCEcXBBqAj8Zxo4SpAiknxs8WvGVTa7eJG3t0yN38ORI5msU6pMOz1Zakm0Zy2wG0Ub44cgEI4A1eKqR/0EgMcO2nsw3nATRbVUoCHzG3fmx+8l6I8GS725uNu8X1YoOT79//srI7nlqmdMgEd1iz2AlZA88NJymoNYrls1E7JiKXxLrM4F0TZloTRUMeNZIY6iG9rizj8Xtsdittf2GPhVk2MHUnuD0yUIFy+16M34t98o717N35kGDzRxYZXymFrVUqZdk2FDgxn1KRtJkbJjr32HSefJBnX+Df1aLq4yEPP2oEowjWF2vY91hfuBEwR2Yjmzv/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, Custom2 = _t, Custom = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}, {"Custom2", type text}, {"Custom", type text}}),


    AddIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

    ScoreRows = Table.SelectRows(AddIndex, each Text.StartsWith([Column], "Question")),
    ScoreRowsExpanded = Table.TransformColumns(ScoreRows, {{"Column", each "Score"}}),
    ScoreRowsFinal = Table.ReplaceValue(ScoreRowsExpanded, each [Custom2], "", Replacer.ReplaceValue, {"Custom2"}),
    ScoreRowsFinal2 = Table.ReplaceValue(ScoreRowsFinal, each [Custom], "1", Replacer.ReplaceValue, {"Custom"}),

    CombinedTable = Table.Combine({AddIndex, ScoreRowsFinal2}),

    SortedTable = Table.Sort(CombinedTable, {{"Index", Order.Ascending}}),

    FinalTable = Table.RemoveColumns(SortedTable,{"Index"})
in
    FinalTable

[barry62]

Thank you very much! it's great.