Forum Discussion

BiBra's avatar
BiBra
Icon for Helper III rankHelper III
8 years ago
Solved

Unpivot columns in table created from DAX epression

Hello I have created a table using a DAX expression. This table contains columns I want to be unpivoted. I there a way to do this?  The reason for using this expression, rather than a normal create...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi BiBra,

     

    Based on above table, to get the latest date for each "target name", please try below M code:

     

    #"Grouped Rows" = Table.Group(#"Previous Step", {"target name"}, {{"AllRows", each _, Value.Type(#"Previous Step")}, {"Latest Date", each List.Max([date]), type number}}), 
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"date", "product1", "product2", "product3"}, {"AllRows.date", "AllRows.product1", "AllRows.product2", "AllRows.product3"})

     

    Then, you can unpivot this table to a new structure.

     

    Regards,

    Yuliana Gu

     

BiBra's avatar
BiBra
Icon for Helper III rankHelper III
8 years ago

For me it is not necessary for it to be M code, DAX table or whatever. I just want the latest data to be retrieved from a query, and turned into a new table. Then I want to be able to unpivot some of the columns. Data is as follows:

 

name           date                product 1            product 2           product 3

hj                 10/12-2016           1                         0                         1

as                  9/10-2016            0                       1                            1

hj                   10/9-2017            1                       1                           1

 

The latest data meaning, I want the latest data record for the specific name.

 

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi BiBra,

 

Based on above table, to get the latest date for each "target name", please try below M code:

 

#"Grouped Rows" = Table.Group(#"Previous Step", {"target name"}, {{"AllRows", each _, Value.Type(#"Previous Step")}, {"Latest Date", each List.Max([date]), type number}}), 
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"date", "product1", "product2", "product3"}, {"AllRows.date", "AllRows.product1", "AllRows.product2", "AllRows.product3"})

 

Then, you can unpivot this table to a new structure.

 

Regards,

Yuliana Gu

 

  • BiBra's avatar
    BiBra
    Icon for Helper III rankHelper III
    8 years ago

    I tried using this code.

    However I still get information information about more than one date, for each target name

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi BiBra,

       

      What else do you want to achieve?

       

      Regards,

      Yuliana Gu

      • BiBra's avatar
        BiBra
        Icon for Helper III rankHelper III
        8 years ago

        I just want only the newest record for each target name. Not more than one. Then I want top be able to unpivot