Forum Discussion
Modifying a query help - adding new column skipping row 1
Hello,
How would I go about adding a new custom column to my query. I want to copy data from one column into a new one, but I want to skip the first row. So the new column would have the same data as the original, but would be 1 row off.
Thank you,
Smoupre raises a good point. This following solution will work if you want the last row to be blank, taking advantage of join semantics in Power Query.
You'll create two indices on your table, the first starting from 0 and the second starting from one. Then you can merge this query with itself using the two indices, which are one off from each other. After self-merging the query, you can remove the index fields and be left with your original, and a copy that is one off. Pictures below:
Add index from 0 (Default name = "Index") and one from 1 (Default name = "Index.1")Merge on Index.1 from the original, and Index on the second copy of the table (Current - bottom)Expand the table of values from the resulting merge and choose only the field you want to duplicateRemove the indices - you do not need them anymorePretty merged table
6 Replies
What happens to the last row?
Smoupre raises a good point. This following solution will work if you want the last row to be blank, taking advantage of join semantics in Power Query.
You'll create two indices on your table, the first starting from 0 and the second starting from one. Then you can merge this query with itself using the two indices, which are one off from each other. After self-merging the query, you can remove the index fields and be left with your original, and a copy that is one off. Pictures below:
Add index from 0 (Default name = "Index") and one from 1 (Default name = "Index.1")Merge on Index.1 from the original, and Index on the second copy of the table (Current - bottom)Expand the table of values from the resulting merge and choose only the field you want to duplicateRemove the indices - you do not need them anymorePretty merged table
One thing that you could try would be to create a second query that only imports the column of interest (the one you want to duplicate). In this query you would then remove the top 1 row. Then you could merge the queries together. Not sure what would happen with the last row in your table for this column, might get an error or might be a blank/null.
Greg_Deckler Merge requires a field to join on. Without the existence of fields to support the off-by-one relationship between the two tables (or the table and itself) there is no way to achieve a merge. Power Query doesn't know how to put two tables next to one another - it understands the relational algebra concept of a JOIN. See my post above on how to achieve the off-by-one field to allow a self-join to create the desired field.
greggyb - Agreed, I guess I was assuming the use of index columns to pull this off, I should have been a little clearer and had more detail in my response. I guess I was still wondering about that last row... :)
Really nice walkthrough of the solution btw.
