Search code examples
excelautomationpowerquery

Merging file with different headers without changing headers

I have multiple files with student data, Column A has the test name (Unit-1, Sem-1, Unit-2, Sem-2) and row 1 is a merged cell header that gives the student name, row 2 gives the all subject name of students enrolled (different column each), and finally the score for each subject in each test and the totals of all the test in the bottom.
Student JOHN ALEX
Subjects ENG MAT SCI ENG MAT SCI
Unit-I 73 74 55 52 71 62
Sem-I 68 72 99 82 64 81
Unit-II 52 52 53 86 52 63
Sem-II 75 58 77 70 90 97
Total 268 256 284 290 277 303

as the headers (student names) are different in each excel file, I'm unable to combine them using the query editor, and also I want to unpivot the data to have the names, subjects, and total in a horizontal arrangement.

Solution

  • Try something like this.

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Top Rows" = Table.Skip(Source,1),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column2", "Column3", "Column4"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Studet", each Source{0}[Column2]),

#"Removed Other Columns2" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column5", "Column6", "Column7"}),
#"Promoted Headers2" = Table.PromoteHeaders(#"Removed Other Columns2", [PromoteAllScalars=true]),
#"Added Custom2" = Table.AddColumn(#"Promoted Headers", "Studet", each Source{0}[Column3]),

Combined = Table.Combine({#"Added Custom",#"Added Custom2"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Combined, {"Subjects", "Studet"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Subjects] <> "Total"))
in  #"Filtered Rows"

    enter image description here

    You can then pivot or group as needed