Forum Discussion
barry62
1 year agoNew Member
insert row with specified values if condition met
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 th...
- 1 year ago
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