Merging resulting to an empty cells

[ExcelNewbie2020]

i have sheet 1, sheet 2 and merge..

i need to merge the data of sheet 1 and sheet 2 in which sheet 1 will be the main sheet and sheet 2 data will be inserted from sheet 1. the reference cell will be the names..
as you can see there are multiple names from sheet 2. how can we merge that will add an empty cell?.. thank you

Untitled spreadsheet

docs.google.com

 

[alansidman]

Using Power Query

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(T1,{{"date", type date}, {"amount", type number}}),
    T2= Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(ChangeType, {"name"}, T2, {"NAME"}, "Table2", JoinKind.FullOuter),
    ET = Table.ExpandTableColumn(MQ, "Table2", {"QTY"}, {"QTY"}),
    #"Grouped Rows" = Table.Group(ET, {"name"}, {{"Data", each _, type table [name=text, date=nullable date, amount=nullable number, QTY=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"name", "date", "amount", "QTY", "Index"}, {"name", "date", "amount", "QTY", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Names", each if [Index] = 1 then [name] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Dates", each if [Index] = 1 then [date] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Amount", each if [Index] = 1 then [amount] else null),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom3",{"Names", "Dates", "Amount", "QTY"})
in
    #"Removed Other Columns1"