Forum Discussion

UBComma's avatar
UBComma
Icon for Helper III rankHelper III
3 years ago
Solved

Create new records from keyword list

AlexisOlson or BA_Pete   Any chance you would have a thought on this? I am trying to add a new column and new records to a table based on whether a list of keywords exists in the 'Title cleaned' col...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    You can link to a cloud file using e.g. Dropbox/Google Drive/SharePoint/OneDrive. Make sure the file doesn't contain any sensitive data before you make it public.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
3 years ago

There's no need to create an index to iterate over. You can replace

each List.Transform(
  List.Transform(
    {
      List.Transform(
        {0 .. List.Count(Keywords) - 1}, 
        (x) => Number.From(List.Contains(Text.Split([Title cleaned], " "), Keywords{x}))
      )
    }, 
    each List.PositionOf(_, 1, 2)
  ){0}, 
  each Keywords{_}
)

with

each List.Intersect({Keywords, Text.Split([Title cleaned], " ")})

 

UBComma's avatar
UBComma
Icon for Helper III rankHelper III
3 years ago

That you very much for your insights and suggestions. This produces the results that I am looking for but it is causing the model to explode in size. This is a test model and only has 2,400 records. The model is under 1 megabyte but when I run the query it says it's loading for hours and hours and grows very large. I let the query run overnight, it finished. I tried loading to the model this morning and it has been running for about 3.5 hours and is currently almost 80 gigabytes.

 

Any ideas why this is happening and how to fix it?

 

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    3 years ago

    Since your data is small, try buffering the table and list into memory before defining the custom column.

     

    Working from cgao's code, this might look like:

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUUrKTFdIKsovz1NITixRitWJVjICihZnJKanVyqk5+ekpOYppOSng2WMgTL5RYl56alwxSYgI3ISk7NBIgqJeSlQw8BaYgE=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Title Index" = _t, #"Title cleaned" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Title Index", Int64.Type}, {"Title cleaned", type text}}
  ),
  BufferedKeywords = List.Buffer(Keywords),
  BufferedTable = Table.Buffer(#"Changed Type"),
  #"Added Custom" = Table.AddColumn(
    BufferedTable,
    "Contains Keyword",
    each List.Intersect({BufferedKeywords, Text.Split([Title cleaned], " ")}),
    type list
  ),
  #"Expanded Contains Keyword" = Table.ExpandListColumn(#"Added Custom", "Contains Keyword")
in
  #"Expanded Contains Keyword"

     

    • UBComma's avatar
      UBComma
      Icon for Helper III rankHelper III
      3 years ago

      Thank you again AlexisOlson - even with the buffering I'm finding Power Query too slow to work with on this query. The query works but takes hours and hours to run, and then hours to load. I'm going to try just using DAX which I'm more comfortable writing. I also might try doing it in Python which I just started dabbling in. I really appreciate your help with M.

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        3 years ago

        Hmm. That's too bad. I'm pretty sure I can get it to work efficiently in Power Query but I'd need an example file to test against.