Forum Discussion

Juli2102's avatar
Juli2102
Frequent Visitor
2 years ago
Solved

Calculate workdays with List.RemoveMatchingItems

Hi,  I would like to identify the amount of workdays between two dates in PowerQuery. Therefore, I create the list [Days] with the days that are between the two dates in the main database. In a ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Juli2102,

     

    you need something like this:

    • replace StepBack with previous_step_reference
    • replace ChangedTypeHoliday[Date] with Table[Column] of your holiday table column reference
    • replace last parameter [Date] with Date column reference in your master table
    = Table.SelectRows(StepBack, each not List.Contains(ChangedTypeHoliday[Date], [Date]))

     

    Whole sample query:

    let
    DataTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9NCgAhCAXgqwyuY0izv7NE97/GhLbwwUCrj5761iJ+n/Mki1KikmmnRRJNxaxEG8NMo7H/q9FaMWswz7M9WnUbYM1sgk0zzrCkOkKT7mmWn3MYuvCNQxm9cWgzD+4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    ChangedTypeData = Table.TransformColumnTypes(DataTable,{{"Date", type date}, {"Value", Int64.Type}}),
    HolidayTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMTpVgdINcUhWtogMpFko0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ChangedTypeHoliday = Table.TransformColumnTypes(HolidayTable,{{"Date", type date}}),
    StepBack = ChangedTypeData,
    FilteredRowsOutHolidays = Table.SelectRows(StepBack, each not List.Contains(ChangedTypeHoliday[Date], [Date]))
in
    FilteredRowsOutHolidays

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Juli2102,

 

you need something like this:

  • replace StepBack with previous_step_reference
  • replace ChangedTypeHoliday[Date] with Table[Column] of your holiday table column reference
  • replace last parameter [Date] with Date column reference in your master table
= Table.SelectRows(StepBack, each not List.Contains(ChangedTypeHoliday[Date], [Date]))

 

Whole sample query:

let
    DataTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9NCgAhCAXgqwyuY0izv7NE97/GhLbwwUCrj5761iJ+n/Mki1KikmmnRRJNxaxEG8NMo7H/q9FaMWswz7M9WnUbYM1sgk0zzrCkOkKT7mmWn3MYuvCNQxm9cWgzD+4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    ChangedTypeData = Table.TransformColumnTypes(DataTable,{{"Date", type date}, {"Value", Int64.Type}}),
    HolidayTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMTpVgdINcUhWtogMpFko0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ChangedTypeHoliday = Table.TransformColumnTypes(HolidayTable,{{"Date", type date}}),
    StepBack = ChangedTypeData,
    FilteredRowsOutHolidays = Table.SelectRows(StepBack, each not List.Contains(ChangedTypeHoliday[Date], [Date]))
in
    FilteredRowsOutHolidays