How to sort imported data from Excel

[Marty46]

Hello,

I'm trying to load data exported from another softawre into Power BI. The data source is an excel spreadsheet where there is a list of workplaces and their ratios. The problem is that the workstations are not in columns next to each other, but on the next row.

For a better idea here is a picture. How to get all the workplaces to be next to each other in the columns.

Thank you for help

[danextian]

Hi @Marty46 ,

Your table isnt in the ideal format for consumption. The workplace columns should be in a single column with a separate row for each distinct workspace and the distinct values in ratio columns should have their own separate columns, something like below:

The workplaces columns are to be unpivoted while the Pivot column is to be pivoted. I've attached a sample pbix for your reference.

[Marty46]

Thank you Danextian,

Lets say that I am going to delete the line "Ratios" and then I need to have all Workplaces in row as per picture. Once the data are in this pattern I will be able to transpose them.

[danextian]

Removing the ratio row will remove the headers. How would you identify then which workplace is which?

[Marty46]

I will nominate the Workplaces as a header. As you can see at the picture I have a list of Wokplaces (300, 310, 320 - till last one 505) Each of those workplace has two ratios (Total Strokes and Total Parts). If woule be possible to somehow move all Workplaces to be next to each I woul be able to work woth the later on in visuals. Thank you

[Arul]

@Marty46 ,

Do you need this?

[Arul]

@Marty46 ,

Do you need as per the image? (or) Is it a sample data?

[Marty46]

Hi Arul,

 you can take them as sample ones. Thank you