Forum Discussion

ChristianRHouen's avatar
ChristianRHouen
Helper III
6 years ago
Solved

Pivoting maximum date for group

Hi all   I want to pivot my data by group and maximum data. See suggested solution below: I'm not sure this is the correct approach to do it, but it was the one that seemed most logical to m...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi,

     

    You should be able to do this with Power query. Refer the Screenshots below. 

    Under Transform -> Group By -> Advanced (Radio Button)

    Under Operation-> Select the Date Column-> Select option MAX()

    Upon calculating the MAX Date Values. Not Select the Column and Click on UnpivotColumns Option.

    Best Regards,
    Vignesh M

    If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue :)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi,

 

You should be able to do this with Power query. Refer the Screenshots below. 

Under Transform -> Group By -> Advanced (Radio Button)

Under Operation-> Select the Date Column-> Select option MAX()

Upon calculating the MAX Date Values. Not Select the Column and Click on UnpivotColumns Option.

Best Regards,
Vignesh M

If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue :)

  • ChristianRHouen's avatar
    ChristianRHouen
    Helper III
    6 years ago

    Thanks for you suggestion

     

    I picked the pivot button instead of unpivot as that is the operation I am looking for.

     

    I get this for the first step, which is correct:

     

    I do however get the error message when I pivot the data:

    "Expression.Error: We cannot convert the value null to type Text.
    Details:
    Value=
    Type=[Type]"

     

    I have looked through my data but there are no Null values.

     

    Can you help?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      You will need to select the list of Columns that you want to Unpivot. Also, Check if the columns have null values. 

      If you are still unsure about this, then Share this PBIX with me and I shall complete it for you.

       

      Best Regards,
      Vignesh M

      If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue :)

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      6 years ago

      hi, ChristianRHouen 

      You could try use Pivot column as below:

      Result:

      you could also add a new custom name that is ""Software" & [ID_software]" and use it for pivot column.

       

      here is sample pbix file, please try it.

       

      Best Regards,

      Lin