Add Date Column Using Power Query

[Anonymous]

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

[HotChilli · Accepted solution]

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