Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I've been busting my head for a few days and cannot seem to accomplish what I feel should be a trivial task.
I have a table with one of the columns being a structured column (Table) looking like this:
Fig. 1
The same table has another basic text column containing:
Fig. 2
I'd like to create a new column with the Custom2 (Fig. 1) content filtered by the PerformanceIndicatorsParsed (Fig. 2).
Expected result for 1st row:
For 4th row:
Here is the code:
let
filter_indicator = () =>
let
output = [PerformanceIndicatorParsed]
in
output,
Filtered = Table.SelectRows([Custom2], filter_indicator()),
Result = Table.FirstN(Filtered,10)
in
Result
Unfortunately, i receive the below error:
Expression.Error: We cannot convert the value "each [PI_Id] = 2 or ..." to type Function.
Details:
Value=each [PI_Id] = 2 or [PI_Id] = 3 or [PI_Id] = 1
Type=[Type]
If i hardcode the condition like below, it works just fine.
let
filter_indicator = () =>
let
output = each [PI_Id] = 2 or [PI_Id] = 3
in
output,
Filtered = Table.SelectRows([Custom2], filter_indicator()),
Result = Table.FirstN(Filtered,10)
in
Result
Any help is greatly appreciated.
Regards
Solved! Go to Solution.
Hi @brassy27,
you were almost there. All you needed was Expression.Evaluate overlay:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpNTM5QiPZMibU1VMgvArMUbBWMlGJ1opWMkOVjSg0MjJON4IrA/FQFU7BKY6BKMMMMxjAHM2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Filter = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Filter", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Changed Type", Expression.Evaluate([Filter])))
in
#"Added Custom"
Cheers,
John
Hi @brassy27,
you were almost there. All you needed was Expression.Evaluate overlay:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpNTM5QiPZMibU1VMgvArMUbBWMlGJ1opWMkOVjSg0MjJON4IrA/FQFU7BKY6BKMMMMxjAHM2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Filter = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Filter", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Changed Type", Expression.Evaluate([Filter])))
in
#"Added Custom"
Cheers,
John
Oh yeah!!! Thanks a million John, you nailed it!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
41 | |
30 | |
26 | |
21 | |
17 |