Calculate workdays with List.RemoveMatchingItems

[Juli2102]

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 second table "tbl_PublicHoliday" I have a column with all public holidays.

Now I want to remove all items from the list [Days] (in the main database) that can be found in the column "Date_Holiday" in the second table ("tbl_PublicHoliday"). I use the following formula foir this: 

List.RemoveMatchingItems([Days], tbl_PublicHoliday[Date_Holiday])

This was also working but now I receive an error saying "The value ""[List]"" can not be converted to type "List". Details: Value=[List] Type=[Type]. 

How can I solve this? 

Many thanks in advance!

Best regards, 

JS

[dufoq3 · Accepted solution]

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...