Forum Discussion

Helper IV

4 years ago

Solved

Finding 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...

Need Help

smpa01

4 years ago

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

4 years ago

vgeldbr as far as understading the funtion, please read two of my blog posts

here and here

Can you please provide a sizeable sample data?

vgeldbr

Helper IV

4 years ago

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
Community Champion
4 years ago
vgeldbr

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.
- smpa01
  Community Champion
  4 years ago
  vgeldbr would be lot easier to debug with sample data
  - vgeldbr
    Helper IV
    4 years ago
    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.