Forum Discussion

fmouhcine's avatar
fmouhcine
Frequent Visitor
2 years ago
Solved

Filter Imported Google Analytics data from pre-defined list in Excel

Dear Community,   I do have to import data from  Google analytics to check the total views per URL. Below is an example of the GA data stracture I'm importing:   Table name = 'Data'   URL ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi fmouhcine, you can us such filter expression (add this as new step, not as a new custom column)

     

    Replace:

    • Table1_Data with your previous step reference
    • Table2_Filter[URL to consider] where Table2_Filter is a table and [URL to consider] is column
    Table.SelectRows(Table1_Data, each List.Contains(List.Buffer(Table2_Filter[URL to consider]), [URL]))

     

    Whole sample query:

    let
    Table1_Data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdJBCsQgDIXhu7gu1MTEpHeY1UBXpcz9bzEkMtTBt3Hh3+iH9LrK+X59qGyF3GLdaefKUu5tSj3XndckLUtby8FZBMx4FgUX2Tiu/6c4qSvSRZED4fJ2F6SL5B3p+HErOE6hLjYb1OXnxoiXyQnxIhkhXg5RQ77Y1Ip48rw4rYVEEC83uSNepm7IF0kN8caQI1+8nDPy6fTmvKZmiDeGFPFyswry6fSX/3z3Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [URL = _t, TTL_views = _t, Date = _t]),
    Table2_Filter = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg3yiTdUitWBsIzgLFOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"URL to consider" = _t]),
    Table1_FilteredByTable2URLs = Table.SelectRows(Table1_Data, each List.Contains(List.Buffer(Table2_Filter[URL to consider]), [URL]))
in
    Table1_FilteredByTable2URLs
  • dufoq3's avatar
    dufoq3
    2 years ago

    If you are 100% sure, that you have /ar/national-auto/get-a-quote and ar/national-auto/find-us in GA-Data table try to use this:

     

    Table.SelectRows(#"Added Items", each List.Contains(List.Buffer(List.Transform(URLs[Url], Text.Trim)), Text.Trim([pagePath])))

     

fmouhcine's avatar
fmouhcine
Frequent Visitor
2 years ago

Hi dufoq3 

 

Please see below the tables' screenshots as requested:

 

"Data" source table :

 

 

"Filter" source table:

 

"Data" source table after applying the filter:

 

dufoq3's avatar
dufoq3
Community Champion
2 years ago

If you are 100% sure, that you have /ar/national-auto/get-a-quote and ar/national-auto/find-us in GA-Data table try to use this:

 

Table.SelectRows(#"Added Items", each List.Contains(List.Buffer(List.Transform(URLs[Url], Text.Trim)), Text.Trim([pagePath])))

 

  • fmouhcine's avatar
    fmouhcine
    Frequent Visitor
    2 years ago

    Many Thanks dufoq3  ! it works perfectly !!

    You were right; I realized that missed the character "/" at the end of each URL in my filters' list, this is why it was not working - my bad ^^!!!

     

    Last but not least : will that be possible to have in my filter the condition : Data[pagePath] contain the string Filter[URL to consider] - instead of Data[pagePath] matches exaclty Filter[URL to consider]

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      OK, so we probably do not need any Text.Trim:

       

      Table.SelectRows(#"Added Items", each 
      [ a = List.Buffer(URLs[Url]),
        b = List.AnyTrue(List.Transform(a, (x)=> Text.Contains([pagePath], x)))
      ][b])