How to insert a row in excel without formula?

[Amuserad]

I have an example.

There are two sheets in an excel, Sheet 1 and Sheet 2

Sheet 1 has

Data 1	50​
Data 2	90​

Sheet 2 has

Data 1

Data 1.5

10​

Here in Sheet 2, Data 1.5 is the subset within Data 1

I would like to insert this Data 1.5 below Data 1 in Sheet1, without manual intervention and without a macro code, just by using formula.

Expected outcome under Sheet 1 is

Data 1	50​
Data 1.5	10​
Data 2	90​

 

[sandy666]

maybe (with your example)

let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    RC = Table.RemoveColumns(Source2,{"Column1"}),
    Ren = Table.RenameColumns(RC,{{"Column2", "Column1"}, {"Column3", "Column2"}}),
    TC = Table.Combine({Source1, Ren}),
    Sort = Table.Sort(TC,{{"Column1", Order.Ascending}})
in
    Sort

Column1	Column2		Column1	Column2	Column3		Column1	Column2
Data 1	50		Data 1	Data 1.5	10		Data 1	50
Data 2	90						Data 1.5	10
							Data 2	90