Join/Pivot two tables with no relations?

[TommyMossberg]

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-06-21	32
2021-06-28	40
2021-07-05	40
2021-07-12	40
2021-07-19	40
2021-07-26	40
2021-08-02	40

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 week	Anna	Stina	Farah	Johan	Kevin
2021-06-21	32	32	32	32	32
2021-06-28	40	40	40	40	40
2021-07-05	40	40	40	40	40
2021-07-12	40	40	40	40	40
2021-07-19	40	40	40	40	40
2021-07-26	40	40	40	40	40
2021-08-02	40	40	40	40	40
...	...	...	...	...	...

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!

[CNENFRNL]

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]

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)

[Anonymous]

@AlexisOlson Slick solution, sir!

[AlexisOlson · Accepted solution]

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]

Super! This did the trick!