Forum Discussion

vgeldbr's avatar
vgeldbr
Icon for Helper IV rankHelper 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's avatar
    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's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

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

 

vgeldbr's avatar
vgeldbr
Icon for Helper IV rankHelper IV
4 years ago

This 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: