Relatively new to Power BI; need help pivoting/transposing a specific way.

[Loretab]

Hello friends,

I've got some data that I want to display a certain way that allows slicers. Here is an example of the format of my data:

ID-----------KPI1---KPI2
Release1---[data]-[data]
Release2---[data]-[data]
Release3---[data]-[data]
Notes-------[note1]-[note2]

Each KPI has a data point for each release as well as notes about that specific KPI. What I've successfully accomplished is reformatting the data so I can use slicers for both the KPI as well as the release. I've done it by transposing and unpivoting. It is as follows:

ID—————Attribute——Value
Release1——KPI1————-[data]
Release1——KPI2—————[data]
Release2----KPI1————-[data]
Release2----KPI2—————[data]
Release3----KPI1————-[data]
Release3----KPI2—————[data]
Notes-------KPI1————-[note1]
Notes------KPI2—————[note2]

The only issue I am having is that I decided I’d like to display the notes in a card somewhere on the page. However, since each of the data is a number, I have to convert to decimal to show properly on other charts I have. So the notes data turns to null when I change the type for the Value column.

Since it seems there is only one data type allowed for each column, is there a way I could instead transform the data to look like this:

ID—————Attribute——Value———Notes
Release1——KPI1————-[data]——-[note1]
Release1——KPI2—————[data]——[note2]
Release2----KPI1————-[data]——-[note1]
Release2----KPI2—————[data]——[note2]
Release3----KPI1————-[data]——-[note1]
Release3----KPI2—————[data]——[note2]
Notes-------KPI1————-[data]——-[note1]
Notes------KPI2—————[data]——[note2]

or is there any other way to get what I’m trying to accomplish?I need some help folks.

Sorry for the terrible format, I don't know how to create a table on this forum.

[v-jiascu-msft]

Hi @Loretab,

Can you share some original data? A dummy one is enough.

How did you change the type? The Notes could be good.

Best Regards,

Dale

[Loretab]

Here's how the data starts:
ID-----------KPI1----------------KPI2
Release1---2.2------------------9.8
Release2---2.1------------------10.6
Release3---1.8------------------9.6
Notes-------Power off time----Restart time
,
I've got it to this form by unpivoting the KPI columns:
ID------------------Attribute------Value
Release1----------KPI1----------2.2
Release1----------KPI2----------9.8
Release2----------KPI1----------2.1
Release2----------KPI2----------10.6
Release3----------KPI1----------1.8
Release3----------KPI2----------9.6
Notes--------------KPI1----------Power off time
Notes--------------KPI2----------Restart time
'
However, in order to use the data correctly in my charts, I have to convert the Value column to decimal. The problem is this sets the "Notes" values to null. Here is my desired result instead:
ID------------------Attribute------Value-------------Notes
Release1----------KPI1----------2.2----------------Power off time
Release1----------KPI2----------9.8----------------Restart time
Release2----------KPI1----------2.1----------------Power off time
Release2----------KPI2----------10.6---------------Restart time
Release3----------KPI1----------1.8----------------Power off time
Release3----------KPI2----------9.6----------------Restart time
'
Any tips on how I can achieve this? Thank you.

[v-jiascu-msft]

Hi @Loretab,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Loretab]

Thank you Dale.

I didn’t realize this could be done easily with a relationship. I am still getting the hang of Power BI but it is a great tool.

[v-jiascu-msft · Accepted solution]

Hi @Loretab,

Since the values are simple and clear, I would suggest deleting the last row and adding a new column. Please check out the demo in the attachment.

if [Attribute] = "KPI1" then "Power Off Time" else if [Attribute] = "KPI2" then "Restart Time" else "Unknown"

Best Regards,

Dale