CODEWITHSUNDEEP
datatablepowerbiexpressiondax
user44943
user44943

Reputation: 23

Generating new table in Power BI dynamically from another table

I am working with Power BI in which I have a list of names in a particular column which has a date attached to each. I need to modify this table to create extra rows that create a further two dates for each name, so each name has 3 rows attached to it, the original, the day before, and the day afterwards.

For example if I had

Person  |  Date
Luke    |  2021-06-01
Adam    |  2021-05-12
Ben     |  2021-04-28

This would be modified to be

Person  |  Date
Luke    |  2021-05-31
Luke    |  2021-06-01
Luke    |  2021-06-02
Adam    |  2021-05-11
Adam    |  2021-05-12
Adam    |  2021-05-13
Ben     |  2021-04-27
Ben     |  2021-04-28
Ben     |  2021-04-29

The dataset I have is many thousands of names. Does anyone know how to create the output in a new table?

Upvotes: 0

Views: 855

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13440

One way to achieve that is to add couple of custom columns, named PrevDate and NextDate for example, as follows:

enter image description here

and

enter image description here

This will give you all 3 dates per person, but in 3 separate columns:

enter image description here

To combine them into a single column, select all date columns and click Transform -> Unpivot Columns:

enter image description here

If you want, you can delete Attribute column, if it is not needed.

Upvotes: 1

Related Questions