Help in creating a Flat Data to a Database

[Rashid Khan]

Dear All,
I am enclosing herewith a sample file with the problem I have.
I have a folder with lot of worksheets for each employee.

Each employee fills up the detail as shown on the Input Sheet in the attached sample.

I would like to have powe query to consolidate each employee's data from the folder and give the result as a database as shown on the output sheet.

Thanks in advance
R Khan

 

[AlanSidman]

Based upon your current example

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column2] <> null)),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"STAFF CODE", "STAFF NAME", "STORE NAME"}, "Attribute", "Value"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Unpivoted Other Columns", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers1",{{"00123", "Staff Code"}, {"Name", "Staff Name"}})
in
    #"Renamed Columns"

 

[Rashid Khan]

Based upon your current example

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column2] <> null)),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"STAFF CODE", "STAFF NAME", "STORE NAME"}, "Attribute", "Value"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Unpivoted Other Columns", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers1",{{"00123", "Staff Code"}, {"Name", "Staff Name"}})
in
    #"Renamed Columns"

Hello Alan
Thanks for your help.

As mentioned in my OP I have several files in a folder, secondly they are not in Table1 format.

To test your code I have to convert the data to a Table1 with Column1, Column2 as my headers
Secondly when I test it with other files then it gives error on the following line:

#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers1",{{"123", "Staff Code"}, {"Name", "Staff Name"}})

As you can see it is hard coding 123 as Staff Code whereas in my other files it would different.

Would you please amend your code so that it reads all the excel file from a folder the range is B5:C15 and then gives the result as a long database with all the Staff Code.

Hope I am clear

Thanks once again for your help and support.

R Khan

 

[Rashid Khan]

Dear Alan,
Would you please look into the solution as per my original post.
Thanks once again for your time and help

R Khan

 

[AlanSidman]

Based upon samples provided, I cannot provide any further assistance.

 

[Rashid Khan]

Thanks for your reply