Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Power Query crazy format

Hi, Human kind will always surprise me… Here is the data input i got : Client    AmountType1        Amount1        AmountType2        Amount2        AmountType3        Amount3 e1        B...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous,

     

    In power query, you may refer to steps below(Unfortunately simply "Unpivot then pivot" can't achieve the result you want):

     

    Firstly duplicate the original table, then you have two tables 'table1' and 'table2', in table1, click on columns [AmountType1], [AmountType2] and [AmountType3] then click Unpivot, remove all the useless columns like below:

     

     

    Then filter the blank values in Value column and add an index column:

     

     

    In table2, click on columns [Amount1], [Amount2] and [Amount3] then click Unpivot, remove all the useless columns and add an index column:

     

     

    Finally, merge table1 and table2 based on index column.

     

     

    You may refer to the appendix.

     

    Regards,

    Jimmy Tao

themistoklis's avatar
themistoklis
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous

 

You can create a New Table using DAX formula, in case you dont want to use Power Query:

New Table = UNION(SELECTCOLUMNS('Table', "Client", 'Table'[Client], "Type", 'Table'[Type 1],  "Amount", 'Table'[Amount 1]), 
                  SELECTCOLUMNS('Table', "Client", 'Table'[Client], "Type", 'Table'[Type 2],  "Amount", 'Table'[Amount 2]),		
                  SELECTCOLUMNS('Table', "Client", 'Table'[Client], "Type", 'Table'[Type 3],  "Amount", 'Table'[Amount 3])  
			  )

Workspace on this link.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi themistoklis,

     

    thanks for the reply, i'll try this way.

     

    I'd prefer to do it in MQuery though (if possible).

     

    Thanks again

    • v-yuta-msft's avatar
      v-yuta-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Anonymous,

       

      In power query, you may refer to steps below(Unfortunately simply "Unpivot then pivot" can't achieve the result you want):

       

      Firstly duplicate the original table, then you have two tables 'table1' and 'table2', in table1, click on columns [AmountType1], [AmountType2] and [AmountType3] then click Unpivot, remove all the useless columns like below:

       

       

      Then filter the blank values in Value column and add an index column:

       

       

      In table2, click on columns [Amount1], [Amount2] and [Amount3] then click Unpivot, remove all the useless columns and add an index column:

       

       

      Finally, merge table1 and table2 based on index column.

       

       

      You may refer to the appendix.

       

      Regards,

      Jimmy Tao

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        Hi,

        It seems to do the trick ! Can't wait to be at work to test (right now, I'm in my bed ^^). Thank you !!