How to transform 2 sets of data with different header/table orientation in a worksheet

Hi musclay13 

You are looking for this, right?

you can see in the M query 2 transformations:

One is for the header and another one for the body then I merged them to get the full view.

You can even re-create the form in pbi with selection of your choice.

The location of my file is in Desktop so you might want to change the location of the folder.

I attached the pbix.

Hope this helps

Hi musclay13 ,

I attached the sample pbix for your review.

You can go through each step to see what is happening.

Basically, I pulled the files via folder, transformed the sample file according to your requirement, loaded them all, and combined them.

If you will see the sample pbix and change the datasource to your own datasource, 

you will see the transformation.

Hope this helps