Remodeling the data from columns to rows based on timestamp/date

[mihaipaculea]

Hi everyone,

I am an medium expierienced user of Excel but quite new to PowerBi. 

I am trying to solve a problem which I suppose will require remodeling the data but I am completly lost and search for advice on how it can be done (if it's possible) 

This is how I currently have data in Power BI.

timestamp	tag1	tag2	tag3	tag4	tag5	tag6	tag7	tag8	tag9	tag10
10/23/2020 13:10	Name1	Name2	Name3	null	Name5	Name6	Name7	Name8	Name9	Name10
10/23/2020 13:15	Name1	Name2	Name3	Name4	Name5	null	null	null	null	null
10/24/2020 13:10	Name1	Name2	Name3	Name4	Name5	Name6	Name7	Name8	Name9	Name10
10/24/2020 13:12	Name1	Name2	Name3	Name4	Name5	null	null	null	null	null

And the table I am aiming to transform into it should look like this one: 

timestamp	TagsName
10/23/2020 13:10	Name1
10/23/2020 13:10	Name2
10/23/2020 13:10	Name3
10/23/2020 13:10	null
10/23/2020 13:10	Name5
10/23/2020 13:10	Name6
10/23/2020 13:10	Name7
10/23/2020 13:10	Name8
10/23/2020 13:10	Name9
10/23/2020 13:10	Name10
10/23/2020 13:15	Name1
10/23/2020 13:15	Name2
10/23/2020 13:15	Name3
10/23/2020 13:15	Name4
10/23/2020 13:15	Name5
10/23/2020 13:15	null
10/23/2020 13:15	null
10/23/2020 13:15	null
10/23/2020 13:15	null
10/23/2020 13:15	null
10/24/2020 13:10	Name1
10/24/2020 13:10	Name2
10/24/2020 13:10	Name3
10/24/2020 13:10	Name4
10/24/2020 13:10	Name5
10/24/2020 13:10	Name6
10/24/2020 13:10	Name7
10/24/2020 13:10	Name8
10/24/2020 13:10	Name9
10/24/2020 13:10	Name10
10/24/2020 13:12	Name1
10/24/2020 13:12	Name2
10/24/2020 13:12	Name3
10/24/2020 13:12	Name4
10/24/2020 13:12	Name5
10/24/2020 13:12	null
10/24/2020 13:12	null
10/24/2020 13:12	null
10/24/2020 13:12	null
10/24/2020 13:12	null

The initial goal of doing this transformation is because I have to perform a COUNT on each date for a specific tag name "Name1" to see how many times that tag firied in a specific time frame. 

If you think it's easier to adjust the COUNT to those many columns instead of changing the table I am completly open for suggestions. 

[amitchandak · Accepted solution]

@mihaipaculea , unpivot the data

https://radacad.com/pivot-and-unpivot-with-power-bi

Video: https://www.youtube.com/watch?v=2HjkBtxSM0g
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[v-kelly-msft · Accepted solution]

Hi  @mihaipaculea ,

Go to query editor>select all the tag columns>unpivot columns:

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak · Accepted solution]

@mihaipaculea , unpivot the data

https://radacad.com/pivot-and-unpivot-with-power-bi

Video: https://www.youtube.com/watch?v=2HjkBtxSM0g
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/