Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ItoDiaz
Helper I
Helper I

Expression.Evaluate with List.MatchesAny

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

StartTable.PNG

 

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"

ResultTable.PNG

 

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"

 

 

2 ACCEPTED SOLUTIONS
camargos88
Community Champion
Community Champion

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"

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Smauro
Solution Sage
Solution Sage

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




Feel free to connect with me:
LinkedIn

View solution in original post

8 REPLIES 8
Smauro
Solution Sage
Solution Sage

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




Feel free to connect with me:
LinkedIn

Hello @Smauro 

 

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

 

Thank you very much 🙂 

Anonymous
Not applicable

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
Not applicable

 

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]))

@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
Not applicable

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
Community Champion
Community Champion

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"

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors