Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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/
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!
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!
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |