Transposing data

[Zaynah16]

Hi guys, 

Please could someone help 

I've got a dataset as follows: 

Pillar             Bu           Sub Bu           overallstrategic goal             type& year 

xxx                xxyhy      hyhhh            Buy X litres of water              annulised target 2022

xxx                hhhh       hyhhh            Buy X litres of paint              annulised target 2023

xxx                xxyhy      hyhhh            Buy X litres of water              annulised target 2024

xxx                hhhh       hyhhh            Buy X litres of paint              annulised target 2025

xxx                hhhh       hyhhh            Buy X litres of paint              overall stratetic target 2025 

I need the table to have the follow columns 

Pillar        Bu     Sub Bu     Annualised target 2022    Annualised target 2023  Annualised target  2025 Annualised target 2025 overall tratetic target 2025

I need the values from type and year column to be as each column value with the strategic goal as the value matching up to each. 

My data current sits in acess and i need to have the visual as above. 

Thank you. 

[edhans · Accepted solution]

In Power Query:

• Click on the Type & Year column
• Go to the Transform Ribbon
• Select Pivot Column
• Set it as follows:
  • 

That should give you what you want.

[AlexisOlson · Accepted solution]

Agreed.

@Zaynah16, you can create a visual like this without needing to reshape your data table at all.

[Zaynah16]

Hi @edhans, 

Thank you for your help! The reason for chnaging the data is for a specic view, and the current format was not working. I tried all of the above and they all worked however i went with doing  Pivot as it was the simplest. 

Thanks 

Zaynah 

[edhans]

Great @Zaynah16 - glad you got a workable solution for your issue.

[BA_Pete]

Hi @Zaynah16 ,

@edhans has given you the solution for your request, but I would ask why you want to make this format change in Power Query.

You're actually changing the data format AWAY from its most efficient, and potentially creating problems for yourself when you come to create relationships and/or measures.

If you just want it to be in this format to display to report users, then you would keep the data in its original structure, but use a matrix visual to report it in the format you're asking for.

Pete

[AlexisOlson · Accepted solution]

Agreed.

@Zaynah16, you can create a visual like this without needing to reshape your data table at all.

[edhans · Accepted solution]

In Power Query:

• Click on the Type & Year column
• Go to the Transform Ribbon
• Select Pivot Column
• Set it as follows:
  • 

That should give you what you want.

[edhans · Accepted solution]

@Zaynah16  As others have suggested, ideally you wouldn't reshape your data, but none of us know your true goal, or that you are even working in Power BI. My solution above will give you the proper table on an Excel worksheet. 

But if you drop your Pillar        Bu     Sub Bu  fields in to a Matrix row, then drop the type& year field into a column, then drop the follwoing measure into the values, then remove totals:

Type and Year = 
CONCATENATEX(
    'Table',
    'Table'[ overallstrategic goal ],
    ", "
)

You can get this:

What the measure does if there are two values, it will concatenate them for you and show both (or 3, or more) in the values field, separated by column.

Maybe you can show me what your original goal was though and let's see if you are going about it the most effective way. 

[edhans]

Hi @Zaynah16 - just checking to see if this helped, of if you have additional questions.