Forum Discussion

TommyMossberg's avatar
TommyMossberg
Helper I
5 years ago
Solved

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 weekTarget working hours
2021-06-2132
2021-06-2840
2021-07-0540
2021-07-1240
2021-07-1940
2021-07-2640
2021-08-0240

 

And the other is a list of employees.

Anna
Stina
Farah
Johan
Kevin

 

I would like to create a table with all the dates in Col A and the names in the other columns and the Target value populated into every employees column.

Start of weekAnnaStinaFarahJohanKevin
2021-06-213232323232
2021-06-284040404040
2021-07-054040404040
2021-07-124040404040
2021-07-194040404040
2021-07-264040404040
2021-08-024040404040
..................

 

How do I make this happen in Power Query?

 

Next step is to supstract actual planned and/or chargeable workhours from target to calculate the theoretical cost of non-chargeable hours.

 

I have about 250 employees and the date-table is calculated the next ten years.

The Emplyee-list updates every month so I don't want to manualy code it.

 

Thanks in advance!

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    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.

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    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.

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNM1MlTSUTI2UorVQRKzAIqZGCCJmesamGKKGRphEbPEFDMyQxez0DWA6Y0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start of week" = _t, #"Target working hours" = _t]),
    Emp = {"Anna","Stina","Farah","Johan","Kevin"},
    Custom1 = let n=List.Count(Emp) in Table.TransformColumns(Source, {"Target working hours", each Table.FromRows({List.Repeat({_},n)}, Emp)}),
    #"Expanded Target working hours" = Table.ExpandTableColumn(Custom1, "Target working hours", Emp, Emp)
in
    #"Expanded Target working hours"

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      5 years ago

      If you're constructing a table, you could also use Table.FromColumns.

       

      let
    Source = DateTable,
    Start = Source[Start of week],
    Target = Source[Target working hours],
    Emp = Employees[Employee],
    n = List.Count(Emp)
in
    Table.FromColumns({Start} & List.Repeat({Target}, n), {"Start of week"} & Emp)