Excel merge two tables, repeating 2nd table lookup row

Question

I have two Excel Tables with a 'lookup' column to merge against. I want to merge to a new table with all the lookup values expanded. If I were doing this in python or some such, the pseudo-code would be something like:

for unique day in Tbl1
  row1 = day
  row2 = ""
  for event in Tbl1 day
    v = event's lookup value in Tbl2
    row1 += event + (len(v) - 1) blank columns
    row2 += v
  print(row1)
  print(row2)   

I'd like to avoid VBA, but would like to use new dynamic array functions (preferred) or power query (if necessary), but I can't figure out how to get the repeat to happen. The power query merges I've tried aren't complete.

The original data (where I've used abbreviations for my real data), has a number of events per day. The 'lookup' column shows the different levels of that event for that day.

Tbl1

day	event	lookup
1	Re	eoni2
1	Gr	eoni1
1	We	eoni1
2	Tn	eoneonii2
2	Ga	eon1
2	Gr	eoni1

Tbl2

lookup	c1	c2	c3	c4	c5	c6	c7	c8
eeononii	E	E	O	N	O	N	I	I
eon1	E	O	N
eoneonii2	E	O	N	E	O	N	I	I
eoni1	E	O	N	I
eoni2	E	E	O	O	N	N	I	I

• Tbl1

  • Data will change: number of events per day, event value, what lookup value might be for an event.
  • The 'event' may or may not repeat from one day to the next, but will be unique within a day.
  • Order (top to bottom) should be maintained in resulting merge (left to right).
  • Max number of days = 3.

• Tbl2

  • generally static and top to bottom order can be changed if needed.
  • may contain entries that are not used by Tbl1.
  • min of 3 and max of 8 values per row.

• Tbl3 output

  • ideally, the 'event' name would not repeat, as shown below, but can if it keeps formula cleaner.
  • the number of columns for each day in output Tbl3 may not be the same, as shown, e.g. day 1 rows have 16 and day 2 rows have 15 here.

The output I want:

Tbl3

day	e1	e2	e3	e4	e5	e6	e7	e8	e9	e10	e11	e12	e13	e14	e15	e16
1	Re								Gr				We
	E	E	O	O	N	N	I	I	E	O	N	I	E	O	N	I
2	Tn								Ga			Gr
	E	O	N	E	O	N	I	I	E	O	N	E	O	N	I

Thanks much.

Answer 1

This can be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range or from within sheet
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

M Code

let

//Read in both tables
//Edit Source and Source1 lines to reflect your actual table names
    Source = Excel.CurrentWorkbook(){[Name="Tbl_1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"day", Int64.Type}, {"event", type text}, {"lookup", type text}}),

    Source1 = Excel.CurrentWorkbook(){[Name="Tbl_2"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source1,
        List.Transform(Table.ColumnNames(Source1), each {_, type text})),

//Join the two tables based on the lookup column
//then remove that column
    #"Join Tables" = Table.NestedJoin(#"Changed Type","lookup", #"Changed Type1","lookup", "joined"),
    #"Removed Columns" = Table.RemoveColumns(#"Join Tables",{"lookup"}),

//Add index column to maintain original Event order
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),

//Expand the joined table and remove the Index column
    #"Expanded joined" = Table.ExpandTableColumn(#"Added Index", "joined", {"c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8"}, {"c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded joined",{"Index"}),

//Unpivot all the "value" columns
//Then remove the "Attribute" column (the previous column Headers)
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns2", {"day", "event"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),   

//Group by "day"
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"day"}, {
        {"event & value", (t)=> let 
            remDay = Table.RemoveColumns(t,"day"),
            //replace all except first event of a type with null
            nullEvents = List.Accumulate(t[event],{}, (state,current)=>
                if state = {} then {current} 
                else if List.Contains(state,current) then state & {null}
                else state & {current}),

            //then create new table and Transpose to get final format    
            newTable = Table.Transpose(Table.FromColumns(
                {nullEvents, t[Value]}            
                ))
        in 
            newTable}
        }),

    //Calculate number of columns for creating column names
    numCols = List.Max(List.Transform(#"Grouped Rows"[#"event & value"], each Table.ColumnCount(_))),

    //expand the grouped columns and set the appropriate names
    #"Expanded event & value" = Table.ExpandTableColumn(#"Grouped Rows", "event & value",
        List.Transform(List.Numbers(1,numCols), each "Column" & Text.From(_)),
        List.Transform(List.Numbers(1,numCols), each "e" & Text.From(_))),

    //Replace alternate "day" with null
    replaceWithNulls = Table.FromColumns(
        {List.Accumulate(#"Expanded event & value"[day], {}, (state,current)=> 
            if Number.IsOdd(List.Count(state)) 
            then state & {null} else state & {current})} & 
            Table.ToColumns(Table.RemoveColumns(#"Expanded event & value","day")),
            Table.ColumnNames(#"Expanded event & value")
            ),


    //set the data types
    typeit = Table.TransformColumnTypes(replaceWithNulls, 
        {{"day", Int64.Type}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(replaceWithNulls),1), each {_, type text}))
in
    typeit