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 musclay13 ,
I was thinking, if your form is standard, then the values will never change the cell numbers, right?
In that case, get the first twelve rows, and transform them into how you need it.
Then get the remaining rows in another table then transform them and merge them together using the file name.
I know there is a better solution, I just want to share my idea to achieve your requrement.
If still need help with your file, shoot me a message for the file and I will send back the pbix for you.
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
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 @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?
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