Forum Discussion
vgeldbr
Helper IV
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 th...
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
Community Champion
Community Championvgeldbr 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}})
