Dec 17 2018
06:30 AM
- last edited on
Jul 12 2019
11:18 AM
by
TechCommunityAP
Dec 17 2018
06:30 AM
- last edited on
Jul 12 2019
11:18 AM
by
TechCommunityAP
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!
Dec 17 2018 09:06 AM
SolutionHi,
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
Dec 17 2018 09:06 AM
SolutionHi,
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