Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
SanchoPL
New Member

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:

DataMeter 1Meter 2Meter 3Meter 4Meter 5Meter 6Meter 7
01.01.202361381810821931021003752338884264742647
01.02.20235730301076662687104983,32477773794837948
01.03.2023609108832223344114977,82116664526945269
01.04.202353865147172284285972,221296674244742447
01.05.202359610196214242215777,78191664396343963
01.06.202370667112727026957027,7891674609046090
01.07.202388303418124231806472,22100004910849108
01.08.202397529919570230974472,22102785004050040
01.09.202384722516790026851861,11102774513245132
01.10.20236839808190437638972,2157694700347003
01.11.202359757382716443948720,861296574202542025
01.12.2023739643100324517276977,844843274541445414

 

And expected result:

dates to be chosen in slicer

Data    01.01.202301.02.2023
Meter 1613818573030
Meter 2108219107666
Meter 331022687
Meter 4100375104983,33
Meter 5233888247777
Meter 64264737948
Meter 74264737948

 

Many thanks for hints if its possible.

Thanks

SanchoPL

1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

4 REPLIES 4
SanchoPL
New Member

You'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!

Sahir_Maharaj
Super User
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
abatahir1
Frequent Visitor

can you please share the sample data in excel 

 

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

"); $(".slidesjs-pagination" ).prependTo(".pagination_sec"); $(".slidesjs-pagination" ).append("
"); $(".slidesjs-play.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-stop.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-pagination" ).append(""); $(".slidesjs-pagination" ).append(""); } catch(e){ } /* End: This code is added by iTalent as part of iTrack COMPL-455 */ $(".slidesjs-previous.slidesjs-navigation").attr('tabindex', '0'); $(".slidesjs-next.slidesjs-navigation").attr('tabindex', '0'); /* start: This code is added by iTalent as part of iTrack 1859082 */ $('.slidesjs-play.slidesjs-navigation').attr('id','playtitle'); $('.slidesjs-stop.slidesjs-navigation').attr('id','stoptitle'); $('.slidesjs-play.slidesjs-navigation').attr('role','tab'); $('.slidesjs-stop.slidesjs-navigation').attr('role','tab'); $('.slidesjs-play.slidesjs-navigation').attr('aria-describedby','tip1'); $('.slidesjs-stop.slidesjs-navigation').attr('aria-describedby','tip2'); /* End: This code is added by iTalent as part of iTrack 1859082 */ }); $(document).ready(function() { if($("#slides .item").length < 2 ) { /* Fixing Single Slide click issue (commented following code)*/ // $(".item").css("left","0px"); $(".item.slidesjs-slide").attr('style', 'left:0px !important'); $(".slidesjs-stop.slidesjs-navigation").trigger('click'); $(".slidesjs-previous").css("display", "none"); $(".slidesjs-next").css("display", "none"); } var items_length = $(".item.slidesjs-slide").length; $(".slidesjs-pagination-item > button").attr("aria-setsize",items_length); $(".slidesjs-next, .slidesjs-pagination-item button").attr("tabindex","-1"); $(".slidesjs-pagination-item button").attr("role", "tab"); $(".slidesjs-previous").attr("tabindex","-1"); $(".slidesjs-next").attr("aria-hidden","true"); $(".slidesjs-previous").attr("aria-hidden","true"); $(".slidesjs-next").attr("aria-label","Next"); $(".slidesjs-previous").attr("aria-label","Previous"); //$(".slidesjs-stop.slidesjs-navigation").attr("role","button"); //$(".slidesjs-play.slidesjs-navigation").attr("role","button"); $(".slidesjs-pagination").attr("role","tablist").attr("aria-busy","true"); $("li.slidesjs-pagination-item").attr("role","list"); $(".item.slidesjs-slide").attr("tabindex","-1"); $(".item.slidesjs-slide").attr("aria-label","item"); /*$(".slidesjs-stop.slidesjs-navigation").on('click', function() { var itemNumber = parseInt($('.slidesjs-pagination-item > a.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); });*/ $(".slidesjs-stop.slidesjs-navigation, .slidesjs-pagination-item > button").on('click keydown', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); var itemNumber = parseInt($('.slidesjs-pagination-item > button.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); }); $(".slidesjs-play.slidesjs-navigation").on('click', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); }); $(".slidesjs-pagination-item button").keyup(function(e){ var keyCode = e.keyCode || e.which; if (keyCode == 9) { e.preventDefault(); $(".slidesjs-stop.slidesjs-navigation").trigger('click').blur(); $("button.active").focus(); } }); $(".slidesjs-play").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-stop").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-play")) { $(".slidesjs-stop").focus(); } } }); $(".slidesjs-stop").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-play").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-stop")) { $(".slidesjs-play").focus(); } } }); $(".slidesjs-pagination-item").keydown(function(e){ switch (e.which){ case 37: //left arrow key $(".slidesjs-previous.slidesjs-navigation").trigger('click'); e.preventDefault(); break; case 39: //right arrow key $(".slidesjs-next.slidesjs-navigation").trigger('click'); e.preventDefault(); break; default: return; } $(".slidesjs-pagination-item button.active").focus(); }); }); // Start This code is added by iTalent as part of iTrack 1859082 $(document).ready(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); $("#tip2").attr("aria-hidden","true").addClass("hidden"); $(".slidesjs-stop.slidesjs-navigation, .slidesjs-play.slidesjs-navigation").attr('title', ''); $("a#playtitle").focus(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").mouseover(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").blur(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#playtitle").mouseleave(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#play").keydown(function(ev){ if (ev.which ==27) { $("#tip1").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); $("a#stoptitle").focus(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").mouseover(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").blur(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").mouseleave(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").keydown(function(ev){ if (ev.which ==27) { $("#tip2").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); }); // End This code is added by iTalent as part of iTrack 1859082
Top Solution Authors