Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Matrix visualization with merged cells data from excel

    Bronze Contributor
    Posted Aug 24, 2022 06:51 AM
      |   view attached
    Hi,

    I have a simple data with product and platform release compatibility. To present the data I am using Matrix visualization.
    I am facing challenge with merged cells in excel data source. There are two header rows in excel data source as shared in test xls file.

    As shared in snapshot below, I have two header rows for platform columns -  Platform Kuber (merged cell) and then next row is its versions Kuber v1, Kuber v2, Kuber v3.

    I would like to create a matrix visual as given below.
    I tried with transpose and then fill down for merged cell values and then transpose again.
    I am not able to create merged cell view in matrix visuals.



    Regards,
    gmp

    ------------------------------
    Gaurav
    ------------------------------

    Attachment(s)

    xlsx
    Test PBIX.xlsx   15 KB 1 version


  • 2.  RE: Matrix visualization with merged cells data from excel
    Best Answer

    Posted Aug 27, 2022 10:29 PM
      |   view attached
    @Gaurav Malpani,
    In order to achieve what you want, you need to transform your input data considerably.​  Specifically, you need to go from the layout that you have in the Excel file (short and fat), to a layout like this (tall and thin):

    To do this, you can use Transform Data  in Power BI.  There are multiple steps involved to get to this result from the original.  Rather than try to describe each step, please just refer to the attached .pbix file and look through the steps in the transform data section.

    Once the data is transformed into this new layout, then setting up the matrix visual is very straight forward.  Just place the Product and Product release columns in the Rows well, the Platform and Version columns in the Columns well, and Value in the Values well.  Note that whatever you place in the Values well has to be a measure, therefore you need to select some aggregation, and, as you see in the example below, I chose to use First.

    The result:

    Hope that helps.

    Kaz.



    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------

    Attachment(s)

    pbix
    malpaniExample.pbix   95 KB 1 version


  • 3.  RE: Matrix visualization with merged cells data from excel

    Bronze Contributor
    Posted 30 days ago
    Edited by Gaurav Malpani 30 days ago
    Hi Kaz,

    Thank you so much for looking into this. It was really helpful.


    Thanks,
    Gaurav


    ------------------------------
    Gaurav
    ------------------------------



  • 4.  RE: Matrix visualization with merged cells data from excel

    Bronze Contributor
    Posted 30 days ago
    Edited by Gaurav Malpani 30 days ago
    Hi Kaz,

    Not Sure how I can mark this thread as SOLVED, could you please help.

    Thanks for your help.

    ------------------------------
    Gaurav
    ------------------------------