locked
Reshaping the columns to rows using DAX in Power BI RRS feed

  • Question

  • Hi,

    I have imported a CSV dataset into Power BI with the following columns.

    KeyCol          Col1          Col2          Col3
    1                  A               B              C
    2                  D               E              <Blank>
    3                  G               <Blank>   I

    I want convert this as follows. (Each column values have to be arranged one below the other along with the key column and column position. Also the <Blank> values have to be removed)

    KeyCol     ColPos     ColValue
    1             1             A
    1             2             B
    1             3             C
    2             1             D
    2             2             E
    3             1             G
    3             3             I

    I can achieve this by duplicating the query to select Col1, Col2 and Col3 in each query respectively, filter the blanks and union them finally. Instead, I want to achieve this using DAX within Power BI?

    Is this possible? Any ideas / suggestions will be helpful.

    with regards,
    Parthiban

    Tuesday, June 13, 2017 7:00 AM

Answers

  • Hi Parthiban,

    Yes, it is possible, you can try to use power query UnpivotOtherColumns.Something like below:
    = Table.UnpivotOtherColumns(#"Changed Type", {"Key"}, "Attribute", "Value")

    Please select Col1,Col2,Col3, then click on Unpivot columns in transform tab,See below image:

    For more information, please refer to Pivot and Unpivot with Power BI.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, June 13, 2017 9:53 AM

All replies

  • Hi Parthiban,

    Yes, it is possible, you can try to use power query UnpivotOtherColumns.Something like below:
    = Table.UnpivotOtherColumns(#"Changed Type", {"Key"}, "Attribute", "Value")

    Please select Col1,Col2,Col3, then click on Unpivot columns in transform tab,See below image:

    For more information, please refer to Pivot and Unpivot with Power BI.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, June 13, 2017 9:53 AM
  • Hi Willson Yuan,

    It worked. Thanks. I am marking this as the answer.

    with regards,
    Parthiban
    Tuesday, June 13, 2017 4:50 PM