Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Multiple Rows into Multiple Columns

  • 1.  Multiple Rows into Multiple Columns

    Posted May 14, 2019 11:54 AM
      |   view attached

    Hi

    I have a table that contains multiple columns with multiple values and I need to format it into a "key value" from the first column with multiple columns made from the values in the second column.  The key column is an office name I then have multiple rows for client, main contractor and operator relating to this office name that I want to show as columns NOT rows i.e. I have one row per office name.

    I have attached the file.

     

    Please help 



    ------------------------------
    Christian Tyler
    IT Manager
    2920548675
    ------------------------------

    Attachment(s)

    pbix
    CH_AL_GLMasterfile.pbix   762K 1 version


  • 2.  RE: Multiple Rows into Multiple Columns

    Top Contributor
    Posted May 15, 2019 04:09 AM
    Edited by Vishesh Jain May 15, 2019 04:11 AM
    Hi @Christian Tyler,

    I saw your file but was unable to edit the query due to some credentials issue.

    However, you can use the query editor to create the key column and put in whatever items from the rows that you want. Using 'columns from examples' should make it easy for you.
    After that you can use the 'Pivot columns' after selecting the new key column and whatever column you want to pivot it by. This will turn all individual items in the key column into columns.

    You can use the calculated column in the table view and use DAX to create the key column as well, but then you wont be able to Pivot the columns, which is why you need to use the query editor.

    Please let me know, if I have not understood your problem.​
    If there is something that I am missing here, please upload some sample of how you want the result to look like.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 3.  RE: Multiple Rows into Multiple Columns

    Posted May 15, 2019 05:20 AM
      |   view attached
    Hi Vishesh

    Apologies the data source was in my one drive I have now imported it as an excel file so should be able to access it.  As mentioned the Site Name is what each row relates to, however I have multiple rows relating to the Client(s), Operator(s) and Main Contractor(s) the Office Name Column identifies these.  I would like to "pivot" this data so there is one row per Site Name and the Client(s), Operator(s) and Main Contractor(s) are separate columns.

    Thanks in advance.


    ------------------------------
    Christian Tyler
    IT Manager
    2920548675
    ------------------------------

    Attachment(s)

    pbix
    Sites.pbix   485K 1 version


  • 4.  RE: Multiple Rows into Multiple Columns

    Top Contributor
    Posted May 16, 2019 01:34 AM
      |   view attached

    Do you want to change the Model or you want to view data as Pivoted ?


    - To change the data as Pivoted you can use "Matrix" table visual and put "Site Name" in Rows and "Office name" in Columns and put measures in Values.

    But when you will Pivot the data whether in Data Model,this will create Column of every unique data in your Office Name. Therefore you will have many columns and it will be very hard for you to handle.

    Therefor I will suggest you to create a Matrix Table and Site name as Filter so that you will have column related to that particular Site only.

    Attached is for your reference.



    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    Xerva
    karachi
    3452523688
    ------------------------------

    Attachment(s)

    pbix
    Sites_.pbix   491K 1 version


  • 5.  RE: Multiple Rows into Multiple Columns

    Posted May 15, 2019 05:20 PM
    Hi Christian,

    So there are a couple of ways to create a unique row, but your goal of 'pivoting' the table should rely not on DAX but the M and the Query Editor. Here are some steps:

    1. On the 'Home' tab, click the 'Edit Query' button
    2. When the editor opens up, select the 'Transform' tab and you'll see the options of 'Pivot Column' or 'Unpivot columns' in the 'Any Column' section

    If I understand your request correctly, you'll want to 'Pivot Column' of those 'Office Id' column. I will say though, this will expand your table into a very large width. This is not optimal for performance reasons. Instead, I'd recommend concatenating the fields 'Office Id' and 'Project Id' to give you a unique value. From there you can create a table with all of the dimensional data you require and make evaluations based off of both of those. Make sense? Here is the M to use:

    Glenigans[Office Id]&"_"&Glenigans[Office Id]

    Hope this helps. Let me know what else I can help with.

    Best,

    ------------------------------
    Dannel Fischer
    ------------------------------



  • 6.  RE: Multiple Rows into Multiple Columns

    Top Contributor
    Posted May 16, 2019 03:16 AM
    Hi @Christian Tyler,

    As everyone else said, I agree to it as well, the data model will become very big if we Pivot tables.

    So can you please send us an excel file or something that shows how you want the final output to look like, so that we can figure out alternate optimal approach for the solution.

    Thank you,​

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 7.  RE: Multiple Rows into Multiple Columns

    Posted May 16, 2019 06:06 AM
      |   view attached
    Hi All

    Yes I have got to a point where using a pivot table creates a large width.  I have attached a spreadsheet outlining the requirement - I have taken the first two site names (key) and on the requirement shown how I would like to see the data.

    ------------------------------
    Christian Tyler
    IT Manager
    2920548675
    ------------------------------

    Attachment(s)

    xlsx
    Sites example.xlsx   631K 1 version


  • 8.  RE: Multiple Rows into Multiple Columns

    Posted May 30, 2019 06:58 AM
    Can anyone help?

    ------------------------------
    Christian Tyler
    IT Manager
    2920548675
    ------------------------------