locked
Parse an excel table, find older date for each month? RRS feed

  • Question

  • I wonder if this can be done using power query functionality.

    There is a table that has hundreds of entries, one column is date and some statistics in other columns.

    In a single month there can be one, many or even no entries.

    I need to form a new list, that is made ONLY by the last entry (and corresponding stats, NOT sums) of each month (if there is one or many for that month) and if possible (no serious problem if this cannot be done), copy the previous month entry if a month has no entries.

    These data will be used to make a chart (with a monthly step).

    NLS


    • Edited by NULUSIOS Friday, June 17, 2016 11:16 AM
    Friday, June 17, 2016 11:15 AM

Answers

  • One way would be this:

    1) Create a table with continuous dates

    2) Merge your table to it with JoinKind.LeftOuter and expand only the columns you want

    3) Check the columns of the expanded table and FillDown (that will allocate the last values to the dates with missing values)

    4) Create a "EndOfMonth" column with Date.EndOfMonth(Date)

    5) Create a filter column with "if [EndOfMonth]=[Date] then "in" else "out" and filter on it. This will leave you with last days of all months only.


    Imke Feldmann TheBIccountant.com

    Friday, June 17, 2016 1:14 PM

All replies

  • One way would be this:

    1) Create a table with continuous dates

    2) Merge your table to it with JoinKind.LeftOuter and expand only the columns you want

    3) Check the columns of the expanded table and FillDown (that will allocate the last values to the dates with missing values)

    4) Create a "EndOfMonth" column with Date.EndOfMonth(Date)

    5) Create a filter column with "if [EndOfMonth]=[Date] then "in" else "out" and filter on it. This will leave you with last days of all months only.


    Imke Feldmann TheBIccountant.com

    Friday, June 17, 2016 1:14 PM
  • I understand. I will try something like that.



    NLS

    Friday, June 17, 2016 1:31 PM