Forum Discussion

ST123's avatar
ST123
New Member
1 year ago
Solved

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 ...
Need Help
  • dharmendars007's avatar
    dharmendars007
    1 year ago

    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 

     

dharmendars007's avatar
dharmendars007
Memorable Member
1 year ago

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's avatar
ST123
New Member
1 year ago

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.