Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I would like to feed my query a list of key words i.e. "Store Acct" and "Do Not Use", and eliminate the rows in my table where those key words occur within the column [Rep Name].
Rather than go through the filter column advanced editor and key in every 'and' condition, I'd like to just maintain a list of keywords (see query below), then use that list to filter through my [Rep Name] column. Is this possible? The below query works only when I include a listing of the entire contents of the field to remove. - Many thanks!
Solved! Go to Solution.
Here's one way to do it in the query editor with a custom filter expression. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JL0pVcExOLlHwz0tVitVBEQopzwcLueQr+OWXKIQWp8JVIQnBVHnlQ6R8E4sqlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Rep Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Rep Name", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each let thisrep = [Rep Name] in List.Count(List.Select({"Do Not Use", "Store Acct"}, each Text.Contains(thisrep, _))) = 0)
in
#"Filtered Rows"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Gorgeous and amazing code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JL0pVcExOLlHwz0tVitVBEQopzwcLueQr+OWXKIQWp8JVIQnBVHnlQ6R8E4sqlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Rep Name" = _t]),
#"List of Keywords" = {"Do Not Use", "Store Acct"},
#"Selected Rows" = Table.SelectRows(Source, each not List.Accumulate(#"List of Keywords", false, (s,c) => s or Text.Contains([Rep Name], c, Comparer.OrdinalIgnoreCase)))
in
#"Selected Rows"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Here's one way to do it in the query editor with a custom filter expression. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JL0pVcExOLlHwz0tVitVBEQopzwcLueQr+OWXKIQWp8JVIQnBVHnlQ6R8E4sqlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Rep Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Rep Name", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each let thisrep = [Rep Name] in List.Count(List.Select({"Do Not Use", "Store Acct"}, each Text.Contains(thisrep, _))) = 0)
in
#"Filtered Rows"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.