1

Since a while back, I have been trying to find an alternative to Power Query in Google Sheets to combine several sheets of data into one by matching on column letter.

For example, in tabA I have the columns X,C,V and Y and in tabB I have the columns X,C,V and U. In a consolidated tab I would like to append the data by column letter, just like it's possible in Excel using Power Query so I would have columns X,C,V,Y and U. I have tried using the query function but it seems to be stacking the data by relative column position rather than matching up the column letters.

Is there any alternative/option to this?

1
  • You should edit your question to clarify your expectation which presumably is that the ranges whose columns you skip would have blank cells instead of values if the columns are aligned.
    – Blindspots
    Commented Apr 3 at 0:34

1 Answer 1

0

Limit by Fully Populated Column

If you are lucky enough to have a fully populated column (X in this example formula), you can use this approach.

  • The LAMBDA q performs each QUERY while also clearing data from any column(s) specified as the second argument.
  • skip columns are based on final position, so to skip column U for Sheet1 if the columns to return are X,C,V,Y,U that would be 5. If skipping X and U that would be {1,5} and if not skipping any columns use 0 or an empty string "".
  • additional sheets can be added simply by adding another q(rng, skip) to the VSTACK function.
=LET(
  q,LAMBDA(rng, skip, LET(arr,QUERY(rng, 
    "SELECT X, C, V, Y, U WHERE X IS NOT NULL",0), col,INDEX(arr,,1), 
      BYCOL(SEQUENCE(1,COLUMNS(arr)), LAMBDA(c, IF(IFNA(MATCH(c,skip,0)),
        INDEX(IFERROR(col/0)), INDEX(arr,,c)))))),
  VSTACK(q(Sheet1!A:Z, 5), q(Sheet2!A:Z, 4)))

Limit by Longest Column

If you can't be certain that any single column is fully populated, then each column you intend to return can be checked for the last populated row and the highest row number is stored in limit. In this way you get all your data but without unnecessary additional rows.

=LET(qTxt,"X, C, V, Y, U", cols,BYCOL(SPLIT(qTxt,","),
    LAMBDA(c, COLUMN(INDIRECT(TRIM(c)&"1")))),
  limit,LAMBDA(x, MAX(BYCOL(cols, LAMBDA(c, 
     MAX(INDEX(IF(INDEX(x,,c)<>"",ROW(INDEX(x,,c)),))))))),
  q,LAMBDA(rng, skip, LET(arr,QUERY(rng, 
      "SELECT "& qTxt &"LIMIT "& limit(rng),0), col,INDEX(arr,,1), 
    BYCOL(SEQUENCE(1,COLUMNS(arr)), LAMBDA(c, IF(IFNA(MATCH(c,skip,0)),
      INDEX(IFERROR(col/0)), INDEX(arr,,c)))))),
  VSTACK( q(Sheet1!A:Z, 5), q(Sheet2!A:Z, 4)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.