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
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
Solved! Go to Solution.
Hi @Juli2102,
you need something like this:
= 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
Hi @Juli2102,
you need something like this:
= 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
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...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |