Error while using the concatenated string to filter multiple columns

[siva_powerbi]

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

[siva_powerbi]

Error while using the concatenated string to filter multiple columns

6m ago

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:

[#"Order #"] = 1

[#"Item #"] = 1

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

[siva_powerbi]

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:

[#"Order #"] = 1

[#"Item #"] = 1

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

[smpa01]

@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=[........]

[siva_powerbi]

Thanks for the answer but issue got resolved.

[smpa01]

@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
   Loop

[siva_powerbi]

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

[wdx223_Daniel · Accepted solution]

sel = Expression.Evaluate("Table.SelectRows(Bottomrows, each "&KeepString&"))",
[Table.SelectRows=Table.SelectRows, Bottomrows=Bottomrows, KeepString=KeepString, Logical.FromText=Logical.FromText])

[siva_powerbi]

Its working,thanks for the solution.

[mahoneypat]

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