locked
Structure Data as Time Series- Power Query RRS feed

  • Question

  • 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


    • Edited by MarioArend Monday, December 17, 2018 2:57 PM
    Monday, December 17, 2018 2:44 PM

Answers

  • Thanks for your answer, but is there any way to make Power Query to automatically identify the list of names that will be displayed in columns? In your answers the list of names appear to be introduced manually {"Albert", "John", "Thomas", "Melinda"}. Thanks!

    Yes, Of course. My apologies. The weather query should be:

    let
        Source = Excel.CurrentWorkbook(){[Name="Weather"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"date", type text}, {"weather", type text}}),
        MergedQueries = Table.NestedJoin(ChangedType,{"date"},Data,{"date"},"Data",JoinKind.LeftOuter),
        ExpansionColumns = List.Difference(Table.ColumnNames(Data), {"date"}),
        ExpandedData = Table.ExpandTableColumn(MergedQueries, "Data", ExpansionColumns),
        ReorderedColumns = Table.ReorderColumns(ExpandedData,{"date"} & ExpansionColumns & {"weather"})
    in
        ReorderedColumns

    Monday, December 17, 2018 5:57 PM

All replies

  • Hi Mario,

    I've used your tables in Excel for this example solution. One table is called 'Data', and the other called 'Weather'. In the Excel tables, I've manually rewritten the date columns as text (e.g. 'Oct-17) to avoid Excel converting to a full date in the current year.

    Transformation for Data source:

    let
        Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"date", type text}, {"name", type text}, {"value", Int64.Type}}),
        PivotedColumn = Table.Pivot(ChangedType, List.Distinct(ChangedType[name]), "name", "value")
    in
        PivotedColumn

    The resulting query is joined (merged) with the Weather query:

    let
        Source = Excel.CurrentWorkbook(){[Name="Weather"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"date", type text}, {"weather", type text}}),
        MergedQueries = Table.NestedJoin(ChangedType,{"date"},Data,{"date"},"Data",JoinKind.LeftOuter),
        ExpandedData = Table.ExpandTableColumn(MergedQueries, "Data", {"Albert", "John", "Thomas", "Melinda"}),
        ReorderedColumns = Table.ReorderColumns(ExpandedData,{"date", "Albert", "John", "Thomas", "Melinda", "weather"})
    in
        ReorderedColumns

    All the steps can be performed in the Power Query Editor.

     
    Monday, December 17, 2018 4:15 PM
  • Thanks for your answer, but is there any way to make Power Query to automatically identify the list of names that will be displayed in columns? In your answers the list of names appear to be introduced manually {"Albert", "John", "Thomas", "Melinda"}. Thanks!

    Monday, December 17, 2018 5:32 PM
  • Thanks for your answer, but is there any way to make Power Query to automatically identify the list of names that will be displayed in columns? In your answers the list of names appear to be introduced manually {"Albert", "John", "Thomas", "Melinda"}. Thanks!

    Yes, Of course. My apologies. The weather query should be:

    let
        Source = Excel.CurrentWorkbook(){[Name="Weather"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"date", type text}, {"weather", type text}}),
        MergedQueries = Table.NestedJoin(ChangedType,{"date"},Data,{"date"},"Data",JoinKind.LeftOuter),
        ExpansionColumns = List.Difference(Table.ColumnNames(Data), {"date"}),
        ExpandedData = Table.ExpandTableColumn(MergedQueries, "Data", ExpansionColumns),
        ReorderedColumns = Table.ReorderColumns(ExpandedData,{"date"} & ExpansionColumns & {"weather"})
    in
        ReorderedColumns

    Monday, December 17, 2018 5:57 PM