Google Sheets Query - transform rows before a query-CodePudding

I have a Google Sheets table similar to this:

| date     | buyer | country | item 1 | item 2 | item 3  | ...
| 2022\1\1 | A.B.  | LAT     | milk   | coffee | sugar   | ...
| 2022\1\2 | C.D.  | GER     | milk   | cocoa  | cookies | ...

Is it possible to transform it somehow to a table, that has only one item per row, example:

| date     | buyer | country | item    |
| 2022\1\1 | A.B.  | LAT     | milk    |
| 2022\1\1 | A.B.  | LAT     | coffee  |
| 2022\1\1 | A.B.  | LAT     | sugar   |
| 2022\1\2 | C.D.  | GER     | milk    |
| 2022\1\2 | C.D.  | GER     | cocoa   |
| 2022\1\2 | C.D.  | GER     | cookies |

So that I can afterward query it for what was sold when and where. In a regular DB, I would have two (or 3) tables with 1-N relations doing a simple join/joins, but I cannot figure out how to do it in google sheets. Any ideas?

CodePudding user response：

Answer

The following formula should produce the result you desire:

=FILTER({{A2:C;A2:C;A2:C},{D2:D;E2:E;F2:F}},NOT(ISBLANK({A2:A;A2:A;A2:A})))

Explanation

This creates an array where each entry in columns A through C is duplicated three times, once for each entry in columns D through F. The =FILTER then returns only rows where column A is not empty.

Note that this is not an easily scalable function if you have a large number of different item columns to parse.

Functions used:

Function References