Help With Dividing Column by Row number

[sparkplug9375]

Hello,

I uploaded a PDF to Power BI. The PDF is not formatted well, so neither is the pbix. I am looking to seperate everything into two rows: headers and columns. After transposing the data, most headers are in the correct position with the data below it correct.

However, some of it IE row 2 it not. Is there a way to take the content in rows 2 (the header) and 3 (the data) into its own column. The header corresponds correctly with row 1

Header Dogs

Row 1   Golden Retriever

Row 2   Cats

Row 3   Tiger

How can Cats become a header in its own column with Tiger?

Thanks!

[v-xuding-msft · Accepted solution]

Hi @sparkplug9375 ,

I create a sample to implement it in Power Query that you can have a try.

• pivot column 

• unpivot columns

• remove columns

• remove alternate rows

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sturlaws]

Hi @sparkplug9375,

on a dataset as simple as you example it is possible:
create a duplicate table
remove bottom 2 rows from table1
remove top 2 rows from table2

transpose both tables

append the tables
transpose the appended table
promote headers

If this is possible in your case will depend on the shape of you data.