Data compilation using Power Query

Since you don't have any query in your workbook. I'm guessing 3 tables are brought in using 3 separate queries.

If so, just unpivot all but [Data Date] and append tables together. Or simply merge tables based on [Data Date] column.

If it's brought in using single query... You'd need additional transformation steps to separate out each table.

Can you confirm how the data is brought in?

The thing is that I download this by one query in exactly the same form as in "data" sheet. Here is my query:

Please Login or Register  to view this content.

I combine this with the defined name "paths" because the link depends on the date -> these are periodic data

Here's how I would do it.

1. Load everything to Query (Tabela1).
2. Demote Headers to first row.
3. Add [Index].
4. Add [Custom] using follwing.

Please Login or Register  to view this content.

5. Filter out nulls using [Column1].
6. Reference Tabela1 and name it fnUnpivot.
7. Added transformation steps (#"Filtered Rows", #"Removed Columns", #"Promoted Headers", & #"Unpivoted Columns").
8. Convert it to function by adding argument for fVal.
Complete "M" for fnUnpivot.

Please Login or Register  to view this content.

9. Add blank query and add source as below, and convert to table.

Please Login or Register  to view this content.

10. Add custom column invoke fnUnpivot([Column1])

11. Expand and remove [Column1]

I have stopped at this point, as I noticed bunch of values that'll need clean up. As you have text mixed in with numeric (1,13*, 1,17*, 1,51*, 19,60*, 22,53* & n.d.).
I wasn't sure how these should be treated.

See attached to steps up to #11.

3 - You add Index column. It doesn't matter if it is 0 base or 1 base in this case. You should find in Add Column ribbon tool.

4 - You add Custom column with the function mentioned. This step is needed to assign grouping to each set of table and to merge/append tables to each other.

If you go into query editor, you can follow steps in Applied steps pane.