Forum Discussion
Collaborate two rows information into one and delete one
Hi,
I have a question on how to set up a new customized column so it can fulfill my project requirements. Here is what I have in power query:
In the image above, I have couple of examples for projects with repeated row under "project" column from different resources. I would like to have a new customized column "Initiative" to replace the column "project initiative". The result will look like below:
For the first two rows, and this is my first example, I would like keep it as the same as "project initiative" column in the new customized "initiative" column because they are two different projects although the both projects have "C" in "project" column.
For the last two rows, and this is my second example, since they are the same project, I would like to delete the last row (by filter out "DELETE" at the end) and move useful information to previous row (which has index of 1).
Can you help me to create the new column called "initiative" which satisfies both examples above? Is there any M code for column "initiative"? Thanks,
3 Replies
We'll need to know how to tell the difference between the two options
"because they are two different projects although the both projects have "C" in "project" column"
"For the last two rows, and this is my second example, since they are the same project,"
Great......That is great question! I can provide more detail.
I had two different excel spreadsheets to be appended in power query, both have one same column called "Project" and other different information (which was not on the screenshot). One excel provides "project" and "project description" columns. And the other provides "project" and "project initiative" columns. After appending both excels, I sorted by "project" then receive the sitation as on the screenshot.
1st example - "to keep"
Although both top two rows have "C" in the "project" column, there are two different names under "project initiative" column, therefore, two different projects.
2nd example - "to collaborate"
After sorting the appended database by "project", I can see if any project appears in both excel spreadsheet and this is the case. When one project in both excels, then I will like to collaborate both excels.
Just so you know, what you see is just snap shot of database, if possible, I would like to retain the original excel as it was given to me and manipulate all the changes in power query. Then I will be easiler to maintain the tool in the future. That is my original thought about this. Thanks, appreciated.
