Forum Discussion
How to get visual like excel table
Hi All,
I have original data input from excel file, format is like below:
| Site | Line | Right formula | Right formula comments | Right labeled consumer pack | Right labeled consumer pack comments | Right outer case | Right outer case comments | Right regulatory code on consumer unit | Right regulatory code on consumer unit comment |
| A123 | sachet 15g/20g/22g/30ml/40ml | N | auto online check2D code by scaner | Y | auto online check2D code by scaner | Y | auto online check1D code by scaner | N | |
| B246 | XL pouch Line1 | N | making1/2 | Y | auto online check2D code by scaner | Y | auto online check1D code by scaner | N | Code Setting: select print model on Printer HMI |
1) I want to visualize in Powerbi and looks like what it shows in excel table below, is there a way? I tried using the matrix, but I cannot get the CM in column and measurements in row? Anyone know how to do that?
2) I want the comments pop up in the table visual, is it possible?
Hello ST123 ,
You need to re-structure the data for meeting your criteria..
1. Your current table needs to be pivoted so that the "CM" values (e.g., Bottle, Sachet, Pouch) become columns, and the measurements (e.g., Right formula/WIP, Right outer case) become rows.
2. In Power Query, you can unpivot the necessary columns to achieve this. You need to unpivot the "CM" categories so that each CM value can become a column in the matrix.
3.In Power BI, you can create a tooltip by dragging the comment field (e.g., "Right formula comments") into the Tooltips area of the Matrix.
Note - The Table visual created in Excel works in Cell Concept but in Power BI its Table concept and for measure to appear in table it needs to have Filter context which is not the same in excel.
If you find this helpful , please mark it as solution and Your Kudos are much appreciated!
Thank You
Dharmendar S
3 Replies
ST123 , In Matrix you have Option Switch Values on Rows to how Measures on Row.
Power BI Matrix Visual- Switch Values to Rows: https://youtu.be/cN8AO3_vmlY?t=11640
For a more complex display
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0
Hello ST123 ,
You need to re-structure the data for meeting your criteria..
1. Your current table needs to be pivoted so that the "CM" values (e.g., Bottle, Sachet, Pouch) become columns, and the measurements (e.g., Right formula/WIP, Right outer case) become rows.
2. In Power Query, you can unpivot the necessary columns to achieve this. You need to unpivot the "CM" categories so that each CM value can become a column in the matrix.
3.In Power BI, you can create a tooltip by dragging the comment field (e.g., "Right formula comments") into the Tooltips area of the Matrix.
Note - The Table visual created in Excel works in Cell Concept but in Power BI its Table concept and for measure to appear in table it needs to have Filter context which is not the same in excel.
If you find this helpful , please mark it as solution and Your Kudos are much appreciated!
Thank You
Dharmendar S
Hi dharmendars007 ,
In my query, the CM already presented as a column, if I do unpivot, it's just create a new column again, no difference versus current. Below is how the raw data looks like, apperciate you can clarify further if my understanding is not correct.
