SOLVED

Structure Data as Time Series- Power Query

Copper Contributor

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

datenamevalue
Nov-17Albert4
Nov-17John3
Feb-18Thomas6
Feb-18Albert7
Apr-18John1
Apr-18Thomas8
Jun-18Melinda5
Jun-18John   9
Jun-18Albert6

 

 Weather
Oct-17sun
Nov-17rain
Dec-17rain
Jan-18snow
Feb-18snow
Mar-18rain
Apr-18rain
May-18sun
Jun-18sun
Jul-18sun
Aug-18sun

 

Goal (time series)

 

 AlbertJohnMelindaThomasWeather
Oct-17    sun
Nov-1743  rain
Dec-17    rain
Jan-18    snow
Feb-187  6snow
Mar-18    rain
Apr-18 1 8rain
May-18    sun
Jun-18695 sun
Jul-18    sun
Aug-18    sun

 

 

Thank you!

 

 

 

1 Reply
best response confirmed by arendmario (Copper Contributor)
Solution

Hi,

 

You may pivot on names - value first table and merge it with second one using right outer join.

image.png

I assume months are defined in same way for both tables, here are as first date of each month - please see attached

1 best response

Accepted Solutions
best response confirmed by arendmario (Copper Contributor)
Solution

Hi,

 

You may pivot on names - value first table and merge it with second one using right outer join.

image.png

I assume months are defined in same way for both tables, here are as first date of each month - please see attached

View solution in original post