Forum Discussion
Finding 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}})
smpa01 any thoughts on how to extract sample data? It is from a very large SQL database. I can grab the first 100 lines but if I post that PBIX you won't be able to connec to the source and I suspect it won't work for you. I can't see any obvious way to take the data in the query off line and post it. I tried DAX Studio but that does not export the reccords and lists in the columns.
In terms of your other response, unfortuntely it looks like in the data ENVIRONMENT is not always at the same location. So, not only is there not a constant number of columns in the record but the record I need is not at a consistent location. And of course the original problem remains is tha the tag name may have typos and other issues with it hence the need for a wildcard search.
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}})