I'm trying to join two Tables with Power Query and I'm helplessly overwhelmed. I created the connection and joint the two tables with a Left Outer Join.

Table 1

Table 2

Now i want to output the following table

I think, there must be a way with the with the Table.ExpandTableColumn command but i have no idea what to search for.

Im using excel 365.

I tried the

Expand industry and date

Click select Name column, right click Group By

Add an index within each group so that we can number the questions. To do this, manually adjust the code generated from the group step so that it ends as follows

{{"data", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), type table}})

Expand the data. Click select the index column and Transform .. pivot column and choose Question as the values column, and in Advanced Options, dont aggregate

full sample code for Table2

let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"Name"}, Table1, {"Name"}, "Table1", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Industry", "Date"}, {"Industry", "Date"}),
#"Grouped Rows" = Table.Group(#"Expanded Table1", {"Name"}, {{"data", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Question", "Industry", "Date", "Index"}, {"Question", "Industry", "Date", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded data", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded data", {{"Index", type text}}, "en-US")[Index]), "Index", "Question")
in  #"Pivoted Column"