Expression.Evaluate with List.MatchesAny

[ItoDiaz]

Hello all,

This is my second post here as I am new using Power BI.

I would need some help regarding a function that I try to use in a query. I have adapted a solution provided by @Smauro  for another question (my first post).

Here my Start Table

I would like to obtain a "ResultTable" so that for each date and for each parameter A to E, if my value is lower / equal to 0.6, my result is true. Otherwise false.

For that I  have tried to use this function : 

GroupList1 = List.Transform(Columns1, each {_, Expression.Evaluate( "each List.MatchesAny(["& _ & "], each _ <= 0.8)", [List.MatchesAny = List.MatchesAny]), Logical.Type})

But my "ResultTable" is wrong. For exemple I should have "false" in my A column for the two dates.  

Here my wrong "ResultTable"

Any Idea about what is wrong in my fonction? Another solution to obtain my "ResultTable" is also welcome. 

Here the entire code :

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLQNTDSNQBxDKDYEI5jdaKVjNCVYWKQMmNsygxQaJAyhKXGCEsxTTNCV4aMIe5EtdQYp6WxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No = _t, Date = _t, A = _t, B = _t, C = _t, D = _t, E = _t]),
PreviousStep = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Date", type date}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}}),
DateList = List.Transform(List.Distinct(PreviousStep[Date]), each {Text.From(_), Percentage.Type}),
Columns = List.Select(Table.ColumnNames(PreviousStep), each _ <> "Date" and _ <> "No"),
GroupList = List.Transform(Columns, each {_, Expression.Evaluate( "each List.Sum(["& _ & "]) / Table.RowCount(_)", [List.Sum = List.Sum, Table.RowCount = Table.RowCount]), Percentage.Type}),
#"Grouped Rows" = Table.Group(PreviousStep, {"Date"}, GroupList),
Columns1 = List.Select(Table.ColumnNames(#"Grouped Rows"), each _ <> "Date"),
GroupList1 = List.Transform(Columns1, each {_, Expression.Evaluate( "each List.MatchesAny(["& _ & "], each _ <= 0.6)", [List.MatchesAny = List.MatchesAny]), Logical.Type}),
#"Grouped Rows1" = Table.Group(PreviousStep, {"Date"}, GroupList1)

//#"Demoted Headers" = Table.DemoteHeaders(#"Grouped Rows"),
//#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
//#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
//#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Date", "Product"}}),
//#"Changed Types" = Table.TransformColumnTypes(#"Renamed Columns", List.Combine({{{"Product", type text}}, DateList}))
in
#"Grouped Rows1"

[camargos88 · Accepted solution]

Hi @ItoDiaz ,

Try this code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLQNTDSNQBxDKDYEI5jdaKVjNCVYWKQMmNsygxQaJAyhKXGCEsxTTNCV4aMIe5EtdQYp6WxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No = _t, Date = _t, A = _t, B = _t, C = _t, D = _t, E = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Date", type date}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"No", "Date"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value] <= 0.6 then true else false),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"No", Int64.Type}, {"Date", type date}, {"A", type logical}, {"B", type logical}, {"C", type logical}, {"D", type logical}, {"E", type logical}})
in
#"Changed Type1"

[Smauro · Accepted solution]

Hi @ItoDiaz 

Supposing you'd like to use a solution like then one you refer to, then:

1) Get a list of the Columns:

    Columns = List.Select(Table.ColumnNames(PreviousStep), each _ <> "Date"),

2a) If your [Date] is distinct, then no need for grouping, you could just Transform the relevant columns:

    TransformList = List.Transform(Columns, each {_, (curr) => curr <= 0.6, Logical.Type}),
    Transform = Table.TransformColumns(PreviousStep,TransformList),

2b) If your [Date] is not distinct, then you can use:

    GroupList = List.Transform(Columns, each {_, Expression.Evaluate( "each List.Min(["& _ & "]) <= 0.6", [List.Min = List.Min]), Logical.Type}),
    Group = Table.Group(PreviousStep, {"Date"}, GroupList),

Note here that List.Min works like List.MatchesAny for numbers: If there is at least one value <=0.6 then the min will also be <=0.6

Cheers,
smauro

[ItoDiaz]

Hello @Smauro 

I have applied your 2a solution and... it works!!!!!

Thank you very much 🙂 

[Anonymous]

try this:

Table.TransformColumns(yourtab,{{"A", each _>0.6},{"B", each _>0.6},{"C", each _>0.6},{"D", each _>0.6},{"E", each _>0.6}})

[Anonymous]

another way to get the result:

Table.FromRecords(Table.TransformRows(yourtab,each _&[A=[A]<0.6,B=[B]<0.6,C=[C]<0.6,D=[D]<0.6,E=[E]<0.6]))

or

Table.FromRecords(Table.TransformRows(yourtab,each [Date=[Date],A=[A]<0.6,B=[B]<0.6,C=[C]<0.6,D=[D]<0.6,E=[E]<0.6]))

[ItoDiaz]

@Anonymous 

Thank you for your proposals. I have used this one for another query where I had fewer columns. In the case of this post I had 30 columns, I needed a faster solution. I should have to precise this detail.

[Anonymous]

to treat a generic list of columns

cols=Text.ToList("ABCDE"),
collist=List.Accumulate(cols,{},(s,c)=>s&{List.Transform(Table.Column(yourtab,c), each _ >0.6)})
in Table.FromColumns(collist, cols)

[camargos88 · Accepted solution]

Hi @ItoDiaz ,

Try this code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLQNTDSNQBxDKDYEI5jdaKVjNCVYWKQMmNsygxQaJAyhKXGCEsxTTNCV4aMIe5EtdQYp6WxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No = _t, Date = _t, A = _t, B = _t, C = _t, D = _t, E = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Date", type date}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"No", "Date"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value] <= 0.6 then true else false),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"No", Int64.Type}, {"Date", type date}, {"A", type logical}, {"B", type logical}, {"C", type logical}, {"D", type logical}, {"E", type logical}})
in
#"Changed Type1"

[ItoDiaz]

@camargos88 

Thanks a lot for your answer.  I didn't use all your solution but I have tried and it works great. 

Still, I learnt a lot regarding others issues ussing your solution and specially working with Table.UnpivotOtherColumns(#"Changed Type", {"No", "Date"}, "Attribute", "Value") for visualisations pourposes.