Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Juli2102
Frequent Visitor

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

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

2 REPLIES 2
dufoq3
Super User
Super User

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

lbendlin
Super User
Super User

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...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors