Forum Discussion
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
Community ChampionHi 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 @mussaenda Yes that is what i wanted!
Thanks for working out the file. How did you get the different forms to combine into one? When i added the data in, it opens up to the below (image). What did you did before hand to have them sorted out?
Like how did you combine all the Headers together first for the Header Query? Cause when i do it there are like repeated headers ( Row 1- Employee Name: Michael; Row 10 Employee Name: James).
How did yours open up to the Employee details (Header Query) all ready in the correct format?
- mussaenda
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
Hi mussaenda I just realise you can transform the "Transform Sample File"!
Alway thought the transformation happens in the "Other Queries". No wonder i cant find where the transformation happen. This is amazing!
This solves what I wanted to achieve! Thank you very much for you time and effort to read through my questions and did the solution with proper naming and structure. Appreciate it alot!