Forum Discussion
Rows headers into columns on the visualisation
Hi,
I have an issue which I try to solve and i am wondering if its possible.
I have a table with data (consumption) which collect data for various meters, each specific meter is a single column.
data are present stored in the calender.
Therefore a value has two tags first time frame in the row and name of the meter in the column.
what i want to do is to list in the rows collumn headers and display data by month in specific column.
I dont want to change the data in the power query but only do it while displaying, is it possible?
Table:
| Data | Meter 1 | Meter 2 | Meter 3 | Meter 4 | Meter 5 | Meter 6 | Meter 7 |
| 01.01.2023 | 613818 | 108219 | 3102 | 100375 | 233888 | 42647 | 42647 |
| 01.02.2023 | 573030 | 107666 | 2687 | 104983,3 | 247777 | 37948 | 37948 |
| 01.03.2023 | 609108 | 83222 | 3344 | 114977,8 | 211666 | 45269 | 45269 |
| 01.04.2023 | 538651 | 47172 | 2842 | 85972,22 | 129667 | 42447 | 42447 |
| 01.05.2023 | 596101 | 96214 | 2422 | 15777,78 | 19166 | 43963 | 43963 |
| 01.06.2023 | 706671 | 127270 | 2695 | 7027,78 | 9167 | 46090 | 46090 |
| 01.07.2023 | 883034 | 181242 | 3180 | 6472,22 | 10000 | 49108 | 49108 |
| 01.08.2023 | 975299 | 195702 | 3097 | 4472,22 | 10278 | 50040 | 50040 |
| 01.09.2023 | 847225 | 167900 | 2685 | 1861,11 | 10277 | 45132 | 45132 |
| 01.10.2023 | 683980 | 81904 | 3763 | 8972,2 | 15769 | 47003 | 47003 |
| 01.11.2023 | 597573 | 82716 | 4439 | 48720,86 | 129657 | 42025 | 42025 |
| 01.12.2023 | 739643 | 100324 | 5172 | 76977,84 | 484327 | 45414 | 45414 |
And expected result:
dates to be chosen in slicer
| Data | 01.01.2023 | 01.02.2023 |
| Meter 1 | 613818 | 573030 |
| Meter 2 | 108219 | 107666 |
| Meter 3 | 3102 | 2687 |
| Meter 4 | 100375 | 104983,33 |
| Meter 5 | 233888 | 247777 |
| Meter 6 | 42647 | 37948 |
| Meter 7 | 42647 | 37948 |
Many thanks for hints if its possible.
Thanks
SanchoPL
Hello SanchoPL,
Can you please try this DAX approach to transform the data dynamically:
MeterConsumptionTable = VAR UnpivotedTable = UNION( SELECTCOLUMNS( 'DataTable', "Meter", "Meter 1", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 1] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 2", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 2] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 3", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 3] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 4", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 4] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 5", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 5] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 6", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 6] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 7", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 7] ) ) RETURN UnpivotedTable
4 Replies
Hello SanchoPL,
Can you please try this DAX approach to transform the data dynamically:
MeterConsumptionTable = VAR UnpivotedTable = UNION( SELECTCOLUMNS( 'DataTable', "Meter", "Meter 1", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 1] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 2", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 2] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 3", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 3] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 4", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 4] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 5", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 5] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 6", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 6] ), SELECTCOLUMNS( 'DataTable', "Meter", "Meter 7", "Date", 'DataTable'[Data], "Consumption", 'DataTable'[Meter 7] ) ) RETURN UnpivotedTableYou've made my day! Its working 🙂
Thanks a lot for quick answer, I woudn't do it myself as I'm new in Power BI environment.
Hi SanchoPL ,
Glad to hear you may have found a solution! If you're sure the issue has been resolved, could you mark this post as resolved? That way, others with similar issues can more easily find a solution and the community can see that the issue has been resolved.
Thanks, and feel free to reach out if you need further help!
can you please share the sample data in excel
