CODEWITHSUNDEEP
excelexcel-formulaoffice365powerquery
Talha Siddiqui
Talha Siddiqui

Reputation: 1

Appending Rows in Excel with Different Column Order, but Same Column Name

I am trying to append two tables into one. I will call them table 1 and table 2 Table 1 has more columns than table 2 because I have added them manually but other column names are the same in both tables. when I append them via power query. table 1s formula is removed and pasted as values automatically. Is there any other way to append these tables? where formulas are not removed and I can subsequently drop the formula to the newly appended table.

Upvotes: 0

Views: 652

Answers (2)

ninMonkey
ninMonkey

Reputation: 7501

"Add queries with the same column names, but different orders"

Append queries in Power Query does exactly that.

enter image description here

or

= Table.Combine({ Query1, Query2 })

Merge requirements

Power query is the opposite of what you're used to:

Source Requirement
Power Query Columns of the same names, with different order
SQL / DAX Columns in the same order, with different names

Upvotes: 0

P.b
P.b

Reputation: 11458

Not a power query solution, but using formula (since you also tagged [excel-formula] and [office365]), you could use:

=LET(header,{"a","b","c"},
          A,A1:C3,
          B,E1:H4,
             rA,ROWS(A),
             rB,ROWS(B),
             ch,COLUMNS(header),
          headerA,INDEX(A,1,),
          headerB,INDEX(B,1,),
                          matchA,XMATCH(header,headerA),
                          matchB,XMATCH(header,headerB),
                                         cleanmatchA,FILTER(matchA,ISNUMBER(matchA)),
                                         cleanmatchB,FILTER(matchB,ISNUMBER(matchB)),
          dataA,INDEX(A,SEQUENCE(rA-1,,2),cleanmatchA),
          dataB,INDEX(B,SEQUENCE(rB-1,,2),cleanmatchB),
                      seqAB,SEQUENCE(rA+rB-1),
IFERROR(
                 IF(seqAB=1,
                     header,
                     IF(seqAB<=rA,
                                              INDEX(dataA,SEQUENCE(rA,,0),SEQUENCE(1,ch)),
                                              INDEX(dataB,seqAB-rA,SEQUENCE(1,ch)))),
                  ""))

enter image description here

Upvotes: 0

Related Questions