Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChristianRHouen
Helper III
Helper III

Pivoting maximum date for group

Hi all

 

I want to pivot my data by group and maximum data. See suggested solution below:

image.png

I'm not sure this is the correct approach to do it, but it was the one that seemed most logical to me.

 

Not 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi,

 

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

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

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

Upon calculating the MAX Date Values. Not Select the Column and Click on UnpivotColumns Option.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 🙂

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi,

 

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

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

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

Upon calculating the MAX Date Values. Not Select the Column and Click on UnpivotColumns Option.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 🙂

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:

image.png

 

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?

hi, @ChristianRHouen 

You could try use Pivot column as below:

1.JPG

Result:

2.JPG

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

3.JPG

 

here is sample pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.