none
Filling in Missing Dates in a query RRS feed

  • Question

  • My query is built from two tables: Actual and Forecast data.  Both are time based.  Typically there is a time gap between the two.  For example, the Actual data ends in May 2016 while my Forecast data begins in September of 2016.  Hence there is a three month gap (June, July, and August) where there is no data.  Since Pivot Tables do not allow scatter charts, my Line Graphs show a "9 month year" for 2016.  How can I fill in the gap while building my query?  Thanks, Pableaux.
    Saturday, October 1, 2016 2:12 PM

Answers

  • There are many ways to achieve this and to choose the best one would require to understand how you're building the rest of your query.

    I'd recommend to use a Calendar-table (on the left side) and join your actuals and forecast figures against it. Then group on (year? and) month (with a sum on your amount).

    Without using a calendar-table you could create a list of dates between your two tables and then extract year and month:

    let
        Source = {Number.From(List.Max(Actuals[Date]))..Number.From(List.Min(Forecast[Date]))},
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
        Year = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Column1])),
        Month = Table.AddColumn(Year, "Month", each Date.Month([Column1])),
        #"Removed Duplicates" = Table.Distinct(Month, {"Year", "Month"}),
        #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Column1"})
    in
        #"Removed Columns"


    Imke Feldmann TheBIccountant.com

    Monday, October 3, 2016 2:06 PM
    Moderator

All replies

  • What do you want to be displayed in these "missing" months?

    Imke Feldmann TheBIccountant.com

    Sunday, October 2, 2016 7:31 PM
    Moderator
  • Nothing.  Its just that when I build a line graph from my pivot table (scatter graphs are not allowed), those missing months are not represented.  So, in the example I presented, year 2016 will show Jan, Feb, Mar, Apr, May, Sep, Oct Nov, Dec.  I need to show all 12 months with the gap having no data.

    Thanks for your reply.  Hope you can help.

    Pableaux

    Monday, October 3, 2016 1:20 PM
  • There are many ways to achieve this and to choose the best one would require to understand how you're building the rest of your query.

    I'd recommend to use a Calendar-table (on the left side) and join your actuals and forecast figures against it. Then group on (year? and) month (with a sum on your amount).

    Without using a calendar-table you could create a list of dates between your two tables and then extract year and month:

    let
        Source = {Number.From(List.Max(Actuals[Date]))..Number.From(List.Min(Forecast[Date]))},
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
        Year = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Column1])),
        Month = Table.AddColumn(Year, "Month", each Date.Month([Column1])),
        #"Removed Duplicates" = Table.Distinct(Month, {"Year", "Month"}),
        #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Column1"})
    in
        #"Removed Columns"


    Imke Feldmann TheBIccountant.com

    Monday, October 3, 2016 2:06 PM
    Moderator
  • Thank you.  I will give this a shot.  I'm not much at writing the code though, I am working with Power Query and Power Pivot (both for the first time).  Very interesting stuff.  Thanks again, Pableaux.

    Pableaux

    Friday, October 7, 2016 1:40 PM