Table Pivot Problem

Power BI
1

I have a problem getting a table to pivot properly. I have attached the pbix file.

TransformIT IOT.pbix (45.3 KB)

There are four columns:
Event ID is the actual record number
EventElementTypeSortCode is the means by which the fields are sorted
EventElementTypeDescription shows the required column headings when pivoted
FreeAnswer contains the data to go into the columns.

You will see from the Query that I have got so far but I cannot get the pivot to work properly.

Paul

2

@PaulBoyes ,

Thanks for providing the PBIX, but unfortunately the data are not local, so I can’t see them. Can you please provide the data in Excel form with a mockup of the result you want to see, and I’ll be glad to give this one a go.

image
image3840×1552 140 KB

Thanks!

  • Brian
3

Hi @PaulBoyes

Is this what you are expecting ?

image
image1297×785 35.3 KB

TransformIT IOT.pbix (60.7 KB)

4

@Rajesh ,

Nice! Did you blow the data out to CSV and then re-import it?

  • Brian
5

Hi @PaulBoyes

Removed unnecessary rows.

image
image1633×368 20.8 KB

TransformIT IOT.pbix (62.3 KB)

1 Like
6

@BrianJ

Copy and Paste.

Copied from Data view and created new table from Enter Data.

1 Like
7

Hi

Yes, that is exactly what I was hoping for. I will have a look now.

Thank you.

Paul

8

Hi

I found a slightly easier way of solving the problem.

Attached is a pbix file where every table is treated exactly the same way.

The queries are identical for each table.

Thank you for the help.

Paul