0

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.

Talha Siddiqui
  • 1
  • You could probably solve this with formula's (INDEX MATCH) rather than power query. But maybe this is helpful https://stackoverflow.com/q/43743926/12634230 – P.b Jun 30 '22 at 08:38
  • Please provide enough code so others can better understand or reproduce the problem. – Community Jun 30 '22 at 15:53

2 Answers2

0

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

P.b
  • 8,293
  • 2
  • 10
  • 25
0

"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
ninMonkey
  • 7,211
  • 8
  • 37
  • 66