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.
Hello everyone,
I've been working with power BI around 4 months, and now I'm starting to get more complicated tasks in my work. I have a large dataset with a date column and several values columns (see annex):
Each "DXX" column represents a time of date in which a value was measured (for example, column D00 means the value was measured at 00:00 from the day of DATE colum (DATE_VAL); column D01 means the value was measured at 00:30... and so each half an hour).
My question is: it is possible to arrange the existing DXX values by time in the date column? I guess some rows with the hour corresponding to each value must be added to the date column.
What I have:
DATE | D00 | D01 | D02 | D03 |
01/01/2020 | 32 | 25 | 24 | 21 |
What I would like:
DATE | VALUE |
01/01/2020 00:30 | 32 |
01/01/2020 01:00 | 25 |
01/01/2020 01:30 | 24 |
01/01/2020 02:00 | 21 |
Thank you so much in advance for your answers,
Cheers!!
Solved! Go to Solution.
Hi,
Please try to unpivot All 'Dxx' columns:
Then split the [Attribute] column:
After Apply&Close, please try to create a calculated column:
Column = FORMAT('Table'[Date]+TIME(0,'Table'[Attribute.2]*30,0),"YYYY-MM-DD HH:MM:SS")
The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
Hi,
Please try to unpivot All 'Dxx' columns:
Then split the [Attribute] column:
After Apply&Close, please try to create a calculated column:
Column = FORMAT('Table'[Date]+TIME(0,'Table'[Attribute.2]*30,0),"YYYY-MM-DD HH:MM:SS")
The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
I'ts perfect! Thank you so much for your help!!
Hi,
Using the power query editor you could unpivot the DXX columns in order to achieve something like what you're after
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |