Search code examples
excelpowerquerym

use a formula to define a column to filter in powerquery

I have an excel source-document with a list of tasks and one column for each weekday containing the time the task runs. I'd like to create another document that pulls information from several sources and combines data. My question how can I select only the tasks that need to run today.

I created the code below; where dayname should return the first 3 letters of each day (those are the headers in the table of the sourcedocument). and then I filter on the not null values of that column.

let
    Source = Excel.Workbook(File.Contents("C:\document.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    dayname= Text.Start(Date.DayOfWeekName(),3),
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Task", type text}, {"Mon", type any}, {"Tue", type any}, {"Wed", type any}, {"Thu", type any}, {"Fri", type any}, {"Sat", type datetime}, {"Sun", type datetime}}),
    #"Filtered Rows" = #!"Table.SelectRows(#""Changed Type"", each ([#dayname] <> null))"
in
    #"Filtered Rows"

edit: Example source data

task mon tue wed
First 6 am 6 am
other 5 am 5 am 5 am
xxx 7 am 8 am 9 am

example result on a tuesday

task today
other 5 am
xxx 8 am

/edit

Solution

  • If you load your data into Power Query.

    enter image description here

    Then unpivot it.

    enter image description here

    The days column heading will be pivoted into an Attribute column.

    The Attribute column values can then be filtered by the current date reformatted as a three letter day name.

    Remove the Attribute column and rename the Value column.

    enter image description here

    The M code:

     let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"task ", type text}, {"mon ", type time}, {"tue", type time}, {"wed", type time}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"task "}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] = 
    Text.Start(
        Text.Lower(
            Date.DayOfWeekName(
                DateTime.Date(DateTime.LocalNow())
            ,"en-us")
        )
    ,3))),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Today"}})
in
    #"Renamed Columns"