Forum Discussion
Solved
Structure Data as Time Series- Power Query
Hello, I am new in Power Query and I want to structure the following original data as a time series structure (I need to leave the values that do not have information in blank).
Original data
| date | name | value |
| Nov-17 | Albert | 4 |
| Nov-17 | John | 3 |
| Feb-18 | Thomas | 6 |
| Feb-18 | Albert | 7 |
| Apr-18 | John | 1 |
| Apr-18 | Thomas | 8 |
| Jun-18 | Melinda | 5 |
| Jun-18 | John | 9 |
| Jun-18 | Albert | 6 |
| Weather | |
| Oct-17 | sun |
| Nov-17 | rain |
| Dec-17 | rain |
| Jan-18 | snow |
| Feb-18 | snow |
| Mar-18 | rain |
| Apr-18 | rain |
| May-18 | sun |
| Jun-18 | sun |
| Jul-18 | sun |
| Aug-18 | sun |
Goal (time series)
| Albert | John | Melinda | Thomas | Weather | |
| Oct-17 | sun | ||||
| Nov-17 | 4 | 3 | rain | ||
| Dec-17 | rain | ||||
| Jan-18 | snow | ||||
| Feb-18 | 7 | 6 | snow | ||
| Mar-18 | rain | ||||
| Apr-18 | 1 | 8 | rain | ||
| May-18 | sun | ||||
| Jun-18 | 6 | 9 | 5 | sun | |
| Jul-18 | sun | ||||
| Aug-18 | sun |
Thank you!
Hi,
You may pivot on names - value first table and merge it with second one using right outer join.
I assume months are defined in same way for both tables, here are as first date of each month - please see attached
1 Reply
Hi,
You may pivot on names - value first table and merge it with second one using right outer join.
I assume months are defined in same way for both tables, here are as first date of each month - please see attached