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

     

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

Hi BiBra,

 

A calculated table is invisible in Query Editor mode. You are not able to unpivot a DAX table. As for how to create a table which retrieves the latest data using M code, please provide sample data of original table and your desired table created by DAX. Also, please illustrate the syntax to filter the latest data.

 

Regards,

Yuliana Gu

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

The data I am working with is as such:

 

target name           date              product1      product 2           product3 

hj                        10/12-17               1                    0                           1

sl                         9/10-17                0                     1                           1

hj                         20/12-17              1                      1                         0

 

So I want to retrieve the latest data for each target name. Then I want to unpivot the products.