Forum Discussion
Create new records from keyword list
- 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.
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"
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.
- AlexisOlson3 years ago
Super User
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.
- UBComma3 years ago
Helper III
How can I send you a test file?
- AlexisOlson3 years ago
Super User
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.