Forum Discussion
TommyMossberg
5 years agoHelper I
Join/Pivot two tables with no relations?
Hi I have two tables I want to join but I don't have any connections between them. One of the tables is a date table looks something like this Start of week Target working hours 2021-...
- 5 years ago
Add the employee list as a custom column, expand that column, then pivot on that column.
let Source = <Date Table Sourcce>, #"Added Custom" = Table.AddColumn(Source, "Custom", each Employees[Employee]), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Custom]), "Custom", "Target working hours") in #"Pivoted Column"In the above, I'm assuming Employees is a separately loaded query with a column named [Employee] that is the list of names.
AlexisOlson
5 years agoSuper User
Add the employee list as a custom column, expand that column, then pivot on that column.
let
Source = <Date Table Sourcce>,
#"Added Custom" = Table.AddColumn(Source, "Custom", each Employees[Employee]),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Custom]), "Custom", "Target working hours")
in
#"Pivoted Column"
In the above, I'm assuming Employees is a separately loaded query with a column named [Employee] that is the list of names.
TommyMossberg
5 years agoHelper I
Super! This did the trick!