Forum Discussion
Add Date Column Using Power Query
Hi all,
I am trying to work through survey data for a restaurant and am having trouble transforming the data. Currently, the raw data looks like this (as they are combined from multiple surveys):
| Survey ID | Attribute | Value |
| 1 | cleanliness | 4 |
| 1 | actionDate | 16/10/2023 |
| 1 | service | 3 |
| 2 | cleanliness | 4 |
| 2 | actionDate | 18/10/2023 |
| 2 | service | 5 |
| 3 | cleanliness | 1 |
| 3 | actionDate | 21/10/2023 |
| 3 | service | 5 |
Is there any way this can be done within Power Query? As you can see, there are dates in the value column. I would like an additional column that takes the action date so ultimately my desired output is:
| Survey ID | Attribute | Value | actionDate |
| 1 | cleanliness | 4 | 16/10/2023 |
| 1 | service | 3 | 16/10/2023 |
| 2 | cleanliness | 4 | 18/10/2023 |
| 2 | service | 5 | 18/10/2023 |
| 3 | cleanliness | 1 | 21/10/2023 |
| 3 | service | 5 | 21/10/2023 |
Duplicate the table.
In Table1, remove the rows with 'actiondate' in Attribute (by filtering from column header)
In Table2, keep only the rows with 'actiondate' in Attribute (by filtering from column header)
--
Merge Table1 and Table2 on SurveyID and expand the new column to return the date
1 Reply
Duplicate the table.
In Table1, remove the rows with 'actiondate' in Attribute (by filtering from column header)
In Table2, keep only the rows with 'actiondate' in Attribute (by filtering from column header)
--
Merge Table1 and Table2 on SurveyID and expand the new column to return the date