I'm working on achieving the following data transformation/wrangling within Power Query but can't seem to get there on my own. i have read a lof of different questions and answers on the forum but it seems just a bit beyond my grasp.
- I have a table which has the ticker of a specific currency in the first column.
- There is a second column with the date and time when a certain event, related to that specific currency, happens. This second column is basically the different 5-minute intervals which exist on any given day.
- Finally there is a third column which describes the magnitude of the event.
The table therefore looks like this
What I would like to do in power Query is transpose the uniques name of the currencies as the first row of a new table. The first column of this table would be the largest time interval for any given currency. In this case, as you can see in the data I am attaching, the largest timeseries would be that of the currency ETH. Using the longest calendar as our first column I would then like to place the values described in item 3 above as rows in the new table.
The new layout would look like this
My steps to transform the raw data in the first table are detailed in this image. Basically just expanding a JSON file and getting all the data I need into that first format which I described previously.
What I then do is:
- Pivot using the first column
- Transpose
That gives me a whole bunch of new columns. Way more than I want. Any idea what I can do differently?
