Forum Discussion
Error while using the concatenated string to filter multiple columns
Hello Experts
I have a requirement to filter the table over multiple columns and the conditon is filter columns are dynamic and are passed through parameter input.
Concatenated the input with and condition and want to use the dynamically created the filter criteria but when I use the step in replace function getting error:
Expression.Error: We cannot convert the value "[#"Order #"] = 1" to type Logical.
input:
after concat
[#"Order #"] = 1 and [#"Item #"] = 1
Code:
let
File = Text.Split(#"SourceTable (2)","/"){0},
Sheet = Text.Split(#"SourceTable (2)","/"){1},
Extract_SV = Text.Split(#"Search_Concat (2)" , ";"),
Source = Excel.Workbook(File.Contents(SourcePath&File), null, true),
Sheet1_Sheet = Source{[Item=Sheet,Kind=#"DataSource_Kind (2)"]}[Data],
Data = Sheet1_Sheet,
Bottomrows = Table.RemoveLastN(Data,4),
create_cond = List.Generate( () => [i = -1, x = List.Count(Extract_SV)],
each [i] < List.Count(Extract_SV),
each [
i = [i] + 1,
extract = Text.Split(Extract_SV{i},","),
p = "[#"""&extract{0}&"""]"&" = "& extract{1},
x = p
],
each [x]
), // Creating the list as above image
remove_1strow = List.RemoveFirstN(create_cond, 1),
KeepString = List.Accumulate(remove_1strow, "", (state, current) => if state = "" then current else state & " and " & current), //concatenating with AND condition 2nd image above
#"Converted to List" = {KeepString},
sel = Expression.Evaluate("Table.SelectRows(Bottomrows, each (KeepString))",
[Table.SelectRows=Table.SelectRows, Bottomrows=Bottomrows, KeepString=KeepString, Logical.FromText=Logical.FromText])
in
sel
In above code when I use the KeepString getting the error Expression.Error: We cannot convert the value "[#"Order #"] = 1" to type Logical.
Please suggest how to overcome this, tried logical conversion functions but with no luck.
Thanks
Siva
sel = Expression.Evaluate("Table.SelectRows(Bottomrows, each "&KeepString&"))",
[Table.SelectRows=Table.SelectRows, Bottomrows=Bottomrows, KeepString=KeepString, Logical.FromText=Logical.FromText])
8 Replies
siva_powerbi Looks like this might be the bug
p = "[#"""&extract{0}&"""]"&" = "& extract{1},can't have " before record [ and and after record ] too
p=[........]
Thanks for the answer but issue got resolved.
Can you share a link to a pbix with representative input data and desired output to troubleshoot directly? or to see if there is a simpler approach to get there?
Pat
sel = Expression.Evaluate("Table.SelectRows(Bottomrows, each "&KeepString&"))",
[Table.SelectRows=Table.SelectRows, Bottomrows=Bottomrows, KeepString=KeepString, Logical.FromText=Logical.FromText])Its working,thanks for the solution.
siva_powerbi if Keepstring gives you error, you can replace List.Accumulate with another List.Generate Loop and see if it generates an error
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilaOUfIvSkktUgAyYhVsFQyVYnUgwp4lqblIorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), L = #"Changed Type"[Column1], Loop = List.Last(List.Generate( ()=> [i=0,j=L{0},k=j,comb=L=""], each [i]<List.Count(L), each [i=[i]+1, j=L{[i]+1}, k=Text.Combine({[k],j}," "), comb=Text.Combine({[k],j},"and ")], each [comb] )) in LoopThanks for the reply.
Your solution looks nice, but issue got resolved by a different answer. Will surely try your solution on monday and will update you.
