Forum Discussion
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 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
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
2 Replies
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-Forum/ba-p/963216
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/1447523Hi 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
