How to get visual like excel table

[ST123]

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?

[dharmendars007 · Accepted solution]

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

LinkedIN 

[ST123]

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.

[amitchandak]

@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