Forum Discussion
Helper IVFinding field names in a list using wildcard using Power Query
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}})
Helper IVFantastic 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
Community Champion- vgeldbr
Thanks. I've read the blog posts and have a basic grasp. I believe I'm stuck because I cannot find any documentation explaining the {6} parameter for Table.Columnames. Microsoft documentation does not mention any parameters.
Table.ColumnNames(Table.FromRecords({[Tags]})){6})
I will try to create a sample data set - my actual data is many millions of rows so need to spend some time doing that unless there is a ready pointer to above.- smpa01
if you look at the following screenshot, in a 0 based inedx (PQ uses 0 based index), ENVIRONMENT's position is 6
and through the following code
Table.SelectRows(Source, each (fx(Table.ColumnNames(Table.FromRecords({[Tags]})){6}) <> "-1"))
I am asking PQ to invoke the custome function(fx) on each element (cell) of TAG hoping when that funtion runs it always find ENVIRONMENT ot variation of ENVIRONMENT in the 6th place.