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
Hi mussaenda !
Thank you for your response. Yes it is standard, I want to make sure that it doesnt change so it wont complicate things.
I manage to figure out how to transform 1 form. However if there are example 10 Forms, i dont know how to even work with the top vertical data as there are repeated headers for each file there is when i do "get data from folder".
Sure thing i will send you a sample folder of 3 forms inside.
Maybe a better context for you, assuming the company has 100 employees, everyone who needs claim will submit this form to finance.
Finance will consolidate them in a folder. Then now want to transform and consolidate all the all data in this folder for reporting/analysis. thats the gist of it.
File Link: Here
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!