Forum Discussion
Solved
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 third column.
becomes
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
4 Replies
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 FinalTableThank you very much! it's great.
Thank you so much.
