Forum Discussion
How to transform 2 sets of data with different header/table orientation in a worksheet
Hi All!
Im pretty new to Power Query and have been trying to figure this out but cant find any solution. I really hope you all can assist me with this.
Background:
- Assuming im creating a workflow for all Expense Claim Form (Excel) from all employees to be saved in a folder.
- From there I will transform all the Forms in this folder to get my consolidated data.
- My Expense Claim Form has 2 portion of data:
i) General information (headers are vertical)
ii) Claim details and description (Headers are horizontal)
Question:
- Im struggling to use Power Query to transform this Form that has 2 separate data in different orientation. How do i transform this data together and is there better workflow?
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
18 Replies
musclay13 it is a separate table format or in one table. Thank You!!
Hi there Mahesh0016 !
Thank you for replying. 🙂
Technically both are not in any table format. They are just cells. As this worksheet is created for data-entry by each individual employee.
musclay13 did you load in power query?
Mahesh0016 I did. I just dont know how to transform all this data into 1. I want to have all these data combine into one.
You can see from the image below, Red are the headers, Blue are the data.
musclay13 so you can first you do pivot the horizontal header table and then append table
I hope this helps you! Thank You!!
Hi Mahesh0016 ,
I'm trying it on GetData from folder. I did a sample just with 2 files only.
If Im just trying to transform the vertical data (headers are in column) first then I append to the horizontal one.
When transforming the vertical data, I see that the headers are repeating itself and the data for 2 files are not combine. You can refer to the image below. What can I do to have all the info of all files combine into the same header from here?
Anyone can help me with this?
Hello,
Like this ?
let Source = Excel.CurrentWorkbook(){[Name="Range"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}}), #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), #"Kept First Rows" = Table.FirstN(#"Removed Blank Rows",3), #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1", "Column2"}), #"Transposed Table" = Table.Transpose(#"Removed Other Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), TopRowsInRecord = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee ID", Int64.Type}, {"Employee Name", type text}, {"Departement", type text}}){0}, BottomRows = #"Removed Blank Rows", #"Removed Top Rows" = Table.Skip(BottomRows,3), #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Added Custom" = Table.AddColumn(#"Promoted Headers1", "Custom", each TopRowsInRecord), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Employee ID", "Employee Name", "Departement"}, {"Employee ID", "Employee Name", "Departement"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}, {"TOTAL", type number}, {"Employee Name", type text}, {"Departement", type text}, {"DESCRIPTION", type text}}) in #"Changed Type1"Hi Mederic , Yes that is something im trying to achieve!
Firstly thank you for spending your precious time to create the scenario and showing me the query. 🙂 Im still quite new to Power Query.
Do I have to type out the M language or is there steps through Power query to achieve what you did?
Also, if I have multiple workbooks of the same form in a folder, can this be performed with "Get data from folder"?
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: HereHi 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
