Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone, ill explain my problem with examples:
I have a table like this:
ID - Message
1 - @asdf
2 - @8rr
3 - 674
4 - 1231@3
etc
In a List, i have the differents values which I want to filter of the previous table, i.e:
List:
@8
@3
What I want is filter the first table, in the case that the message contain ANY of the values of the list, so my expected result would be:
1 - @asdf
3 - 674
Check that its CONTAINS and not EQUAL.
Ok, lets go to M code, after my test, I could solve partially the problem, I achieve to filter if the message is Exactly the string in the List, with the following code:
= Table.SelectRows(#"Replaced Value1", each not (List.Contains(List, [message])))
After a research, Ill try this code, but it does not work:
= Table.SelectRows(#"Replaced Value1", each not (List.MatchesAny(List, each Text.Contains([message],_))))
It brings me the following error:
Expression.Error: We cannot apply field access to the type Text.
So, any ideas of how to achieve this?
Thanks in advance
Solved! Go to Solution.
@aoliver here is how you can do it
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTQVXBILE5JU4rViVYyAvEsiorAHGMgx8zcBMw2AbINjYwNHYyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x) => List.MatchesAny(SearchList, each Text.Contains(x[Column1], _)))
in
#"Added Custom"
here is the query for SearchList
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrBQitUBUsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
Column1 = #"Changed Type"[Column1]
in
Column1
Would appreciate Kudos 🙂 if my solution helped.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@aoliver here is how you can do it
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTQVXBILE5JU4rViVYyAvEsiorAHGMgx8zcBMw2AbINjYwNHYyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x) => List.MatchesAny(SearchList, each Text.Contains(x[Column1], _)))
in
#"Added Custom"
here is the query for SearchList
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrBQitUBUsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
Column1 = #"Changed Type"[Column1]
in
Column1
Would appreciate Kudos 🙂 if my solution helped.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Sorry for mention you again, my problem has changed a little:
It is possible to filter 2 times in 2 different columns? Let me explain with an example:
My table
ID - STATUS 1 - STATUS 2
1 - TD - #8
2 - TD - #5
3 - TM - #5
I want to filter with AND not with Any, for example (in my list)
List 1 List2
TD #8
TM #5
So, List1 filters status1 and List 2 filters status2,
so the result would be
2 - TD - #5
There is a problem: the value of the list is not the whole value of status columns, its just a part of it.
Have the example sense? Any ideas?
Ive tried to create 2 conditional columns but that is a OR not an AND, in logical forms.
Thanks again
Amazing, this is exactly what I looking for, THANKS!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |