none
Power query: Multi date range filter. RRS feed

  • Question

  • I have 2 table: 1 is mass data to filter(Dates, Names, Moneys). 1 is condition table.

    Tbl2: like sample:

    Index   names   Startday   Endday

    1   A   01/01/2012   01/05/2013

    2   A   01/02/2014   01/05/2014

    3   A   01/08/2014   01/07/2016

    4   B   01/01/2012   01/05/2016

    I just need to help that how to filter multi ranges of date [Startday]-[Endday]. I know that's easy with PowerBI, but i cant figure it out with Power Query. Any suggest will be appreciate. Thanks.

    P/s: Dates format: EU.

    Thursday, March 16, 2017 12:58 AM

Answers

  • Alternatively you can merge the 2 tables on name with an inner join, add an index to remove any duplicates at the end, expand Table, filter rows with Date between Start- and EndDay, remove duplicates and remove columns.

    let
        Source = Table.NestedJoin(Table1,{"Name"},Table2,{"Name"},"NewColumn",JoinKind.Inner),
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Added Index", "NewColumn", {"StartDay", "EndDay"}, {"StartDay", "EndDay"}),
        #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each [Date] >= [StartDay] and [Date] <= [EndDay]),
        #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Index"}),
        #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"StartDay", "EndDay", "Index"})
    in
        #"Removed Columns"

    • Marked as answer by Hao Ninh Xuan Friday, March 17, 2017 2:00 AM
    Thursday, March 16, 2017 5:38 AM

All replies

  • You can add a column where a list of all dates will be created like this:

    List.Transform({Number.From([Startday])..Number.From([Endday])}, each Date.From(_)))

    Then you expand that column and will end up with a table that has one row per day.

    Merge this with your Fact-table on JoinKind.Inner and it will act as a filter.


    Imke Feldmann TheBIccountant.com

    Thursday, March 16, 2017 5:32 AM
    Moderator
  • Alternatively you can merge the 2 tables on name with an inner join, add an index to remove any duplicates at the end, expand Table, filter rows with Date between Start- and EndDay, remove duplicates and remove columns.

    let
        Source = Table.NestedJoin(Table1,{"Name"},Table2,{"Name"},"NewColumn",JoinKind.Inner),
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Added Index", "NewColumn", {"StartDay", "EndDay"}, {"StartDay", "EndDay"}),
        #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each [Date] >= [StartDay] and [Date] <= [EndDay]),
        #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Index"}),
        #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"StartDay", "EndDay", "Index"})
    in
        #"Removed Columns"

    • Marked as answer by Hao Ninh Xuan Friday, March 17, 2017 2:00 AM
    Thursday, March 16, 2017 5:38 AM
  • Great one, thanks all guys ^^!

    • Marked as answer by Hao Ninh Xuan Friday, March 17, 2017 2:00 AM
    • Unmarked as answer by Hao Ninh Xuan Friday, March 17, 2017 2:00 AM
    Friday, March 17, 2017 2:00 AM