Hello all,
I am trying to clean up a report in BI that comes from an Excel file and could use some guidance on how to set up a new column with some logic involved. In the excel spreadsheet there are basically multiple rows with headers for projects in a table. The headers are all the same.
In the report it has headers like the following
NAME | PROJECT MANAGER | PROJECT ID
However, in the NAME column there are values in the column that I want to have added into a newly created column, call it STATUS
So the way the data looks now would be example:
| Project Name | Project Manager | Project ID |
| In progress | null | null |
| Project Name | Project Manager | Project ID |
| Project 1 | Bob | 1 |
| Project 2 | Bob | 2 |
| On hold | null | null |
| Project Name | Project Manager | Project ID |
| Project 3 | John | 3 |
| Complete | null | null |
| Project Name | Project Manager | Project ID |
| Project 4 | Steve | 4 |
| Canceled | null | null |
| Project Name | Project Manager | Project ID |
| Project 5 | Steve | 5 |
Ideally, what I would like to do is take the 'In Progress', 'On Hold', 'Complete', and 'Canceled' values that are in the NAME column and insert a new column to the end so it is like
| Project Name | Project Manager | Project ID | Status |
| Project 1 | Bob | 1 | In Progress |
Then, remove the rows that include the headers for that section so that the data in the table is properly formatted with only 1 correct set of headers at the top of table.
Could anyone assist with the clean up? It would be greatly appreciated!
