0

I have two sheets where I have the number of days a person worked per week. I have date of the Monday of the week as the header. I want to have a dynamic table where I can see the sum of days each person worked each week while consolidating the weeks too. In the screenshots you can see in Sheet1 the weeks are from 23 Jan to 13 Feb and in Sheet2 they are 2 Jan to 30 Jan. So in this new dynamic sheet I want to see the row headers as 2 Jan to 13 Feb and I want to sum the number of hours for each week for each person. Note: the names are not in the same order. In the resulting table, for 23 Jan Tracy should have a sum of 3. Can someone please help? I've tried pivot tables and a few Power Query tutorials but can't imagine how to get this working

sheet1 sheet2

Yohan Blake
  • 1,298
  • 4
  • 21
  • 43

1 Answers1

1

In powquery you could just bring the two table in as separate queries, massage it, and return data to a pivot table to get whatever you want

For example take your data and load the two tables into powerquery. Here they load as Table4, and Table5.

enter image description here

Then massage and combine them

let  Table1= Table.UnpivotOtherColumns(Table4, {"Name"}, "Date", "Value"),
Table2= Table.UnpivotOtherColumns(Table5, {"Name"}, "Date", "Value"),
Combined = Table.Combine({Table1,Table2}),
#"Changed Type" = Table.TransformColumnTypes(Combined,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Week", each Date.WeekOfYear([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Year", each Date.Year([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Count", each 1,type number)
in  #"Added Custom2"

That gets you this, which you can load as a pivot table to get any of your items

enter image description here

horseyride
  • 17,007
  • 2
  • 11
  • 22
  • Great, thanks. I've gotten somewhere now. How can I add the original table name as a column in the combined table? I want to show where the value came from – Yohan Blake Feb 22 '23 at 23:01
  • Add column, custom column to original tables, and later change the unpivot to unpivot two and not one column names – horseyride Feb 22 '23 at 23:17