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

[musclay13]

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?

[mussaenda · Accepted solution]

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

[mussaenda]

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

[musclay13]

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

[mussaenda · Accepted solution]

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

[musclay13]

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 · Accepted solution]

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

[musclay13]

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! 

[musclay13]

Hi @mussaenda just one last question! Im trying to learn and understand in the Custom Column for Authorized By:
1. What is the use of the TRY function in this and not just use IF?
2. Whats the purpose of Text.Upper in this context?

[mussaenda]

Hi @musclay13 ,

I am using Text.Upper so it will be case insensitive.

I am using try-otherwise to avoid any errors. it is like iferror function in excel

Hope this helps 

[musclay13]

Hi @mussaenda I got it now. Thank you very much! 😊

[musclay13]

Anyone can help me with this? 

[Mederic]

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"

[musclay13]

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"?

[Mahesh0016]

@musclay13  so you can first you do pivot the horizontal header table and then append table

I hope this helps you! Thank You!!

[musclay13]

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?

[Mahesh0016]

@musclay13 did you load in power query?

[musclay13]

@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.

[Mahesh0016]

@musclay13  it is a separate table format or in one table. Thank You!!

[musclay13]

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.