Creating new table that merges mulitple date columns into multiple rows instead

[lukeguy12]

Hello!

I'm working with a dataset that includes the below:

Address	Date 1	Date 2	Date 3	Date 4
Fake Street, London	01/01/2024	05/06/2022
Real Street, Norwich	05/05/2023	05/01/2021	01/01/2020

To make working with dates easier, I want to create a new table which basically transposes the multiple date columns into multiple rows. So for example, my new table would look like the below:

Address	Date
Fake Street, London	01/01/2024
Fake Street, London	05/06/2022
Real Street, Norwich	05/05/2023
Real Street, Norwich	05/01/2021
Real Street, Norwich	01/01/2020

Struggling to acheive this in the Desktop app and would appreciate any help. The dataset is tricky but it can't be changed without impacting the team's workflow and i'd rather not do that.

Thanks!

[MNedix]

Well, one way to do it is first to duplicate your table in Power Query (rightclick on table -> Duplicate), then unpivot, then delete the columns you're not interested in.

[MNedix · Accepted solution]

Hi,

If you unpivot the other columns like in the screenshot below you will get the desired result.

If this answers your question then please mark it as the solution so others can see it.

[lukeguy12]

Thank you for the quick reply! This gets me closer but having issue only having the values I need in the resulting columns. The other columns in the dataset are being included too. Any ideas?

Update: nevermind, solved it.

for those with similar issue, I simply did the following:

Transform Data > right click table > Reference > Removed all other columns in reference table > did the unpivot solution as suggested here.

[MNedix]

That works too 🙂