Forum Discussion
Helper IVFinding field names in a list using wildcard using Power Query
I have a table that has a column called Tags each row of which contains a list. In each list there is a set of field/value pairs (e.g. ENVIRONMENT and Production").
I would like to extract all the field names from the list and check if the word "environment" appears as a fieldname. But I also want to check of " environment" or "environment " exists along with any other typical typos like incorrect/different case. I cannot find any way of using a wildcard such as "*environment*" in my transformation step.
I'm trying to use this set of functions:
= Table.AddColumn(#"Added Custom", "TagNames", each List.FindText( Record.FieldNames([Tags]),"ENVIRONMENT"))
This works but only for the exact match (including case) on "ENVIRONMENT".
vgeldbr try this dynamic version and you need to adapt to your scenario
let fx=(input)=> Web.Page( "<script> var x='"&input&"'; var b=x.search(/environment/gmi); document.write(b); </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text], Source = #table({"ColumnA", "ColumnB"}, {{[ENVIRONMENT=1,Data=1],1},{[environment=1,Data=1],2},{[ environment=1,Data=1],3},{[environment =1,Data=1],4},{[environ=1,Data=1],5},{[Data=1,ENVIRONMENT=1],6}}), #"Added Custom" = Table.AddColumn(Source, "Custom", each let x = Table.FromList(Table.ColumnNames(Table.FromRecords({[ColumnA]}))), y= Table.FromList(x[Column1]), z = Table.AddColumn(y,"Custom",each fx([Column1])), a=Table.SelectRows(z,each [Custom]<>"-1") in a), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each (Table.IsEmpty([Custom]) = false)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}) in #"Removed Columns"I started with a differnt data as following coordinate of ENVIRONMENT
Source = #table({"ColumnA", "ColumnB"}, {{[ENVIRONMENT=1,Data=1],1},{[environment=1,Data=1],2},{[ environment=1,Data=1],3},{[environment =1,Data=1],4},{[environ=1,Data=1],5},{[Data=1,ENVIRONMENT=1],6}})
11 Replies
smpa01Community Champion
vgeldbr yes it possible
let fx=(input)=> Web.Page( "<script> var x='"&input&"'; var b=x.search(/environment/gm); document.write(b); </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text], Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs0ryyzKz8tNzStRitWJVlJAF0DiKyALKMXGAgA=", 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}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each (fx([Column1]) <> "-1")) in #"Filtered Rows"Reference - https://community.powerbi.com/t5/Community-Blog/Using-JavaScript-in-power-query-for-regex-Part2/ba-p/2132960
Pbis is attached
vgeldbrThis is great! But still stuck because my column contains a record and the record may or may not contain the Tag "ENVIRONMENT" or any of the variants I mentioned. I am able to extract the ENVIRONMENT field from the record if it exists but I need to be able to extract all variations of the field name not just ENVIRONMENT. See two screenshots:
- smpa01
vgeldbr still dobale
let fx=(input)=> Web.Page( "<script> var x='"&input&"'; var b=x.search(/environment/gmi); document.write(b); </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text], Source = #table({"ColumnA", "ColumnB"}, {{[ENVIRONMENT=1,Data=1],1},{[environment=1,Data=1],2},{[ environment=1,Data=1],3},{[environment =1,Data=1],4},{[environ=1,Data=1],5}}), #"Filtered Rows" = Table.SelectRows(Source, each (fx(Table.ColumnNames(Table.FromRecords({[ColumnA]})){0}) <> "-1")) in #"Filtered Rows"in your case , you need to change the index number and it will be (looking at the screenshot you provided)
#"Filtered Rows" = Table.SelectRows(Source, each (fx(Table.ColumnNames(Table.FromRecords({[Tags]})){6}) <> "-1"))
- vgeldbr
Fantastic and almost there. I don't understand the function part fully and the solution appears to be failing where the number of items in the record is 6 or fewer. My screenshot example had 7 columns in the record but in fact as these are tags there could be any number from none to infinite. I'm guessing that the issue relates to the value of 6 in the filter argument:
(fx(Table.ColumnNames(Table.FromRecords({[Tags]})){6}) <> "-1")
But I don't understand the function itself to figure out why:
let
Source = (input)=>
Web.Page(
"<script>
var x='"&input&"';
var b=x.search(/environment/gmi);
document.write(b);
</script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text]
in
Source