I'm building a PBI report which categorises company reviews based on the review content, and outputs the relevant topics in a new column. I'm very new to Power Query and Power BI although I've managed to figure things out up to now, but this has me stumped.
I have used a version of the solution in this thread to give me the output I needed, which was multiple topics in one column, separated by a comma (which I then split to go onto separate rows).
This worked but only when I pasted some sample data into a blank query to get the source in the Json.Document format as per the solution on the link:
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkoty0wtNzQyNjE1U9JRMrLQNzDXNzIwMlIwNLMyMgUKuaTmZJalFlUCmSEZqQopUK5CeWKxQlpicUlqkUJJRmKegqdCakVBanJJaooeUClIp6NbcJkRiHaCmh+rA7MQzDdHs9DYysACKOSUn59drJBYVAS0J0WhsDQzOTunUiExL0UhByjzqGEZQTUKmXlp+UW5iSVAWTS3OLtArY6NBQA=", BinaryEncoding.Base64), Compression.Deflate))
What I didn't realise was that the string would change as the data in the query was appended! By the time I'd added more rows, the Json string was nearly 33,000 characters.
As I'm going to be adding new rows to the data every month, I'd prefer to use a CSV as the source instead of pasting data - which has a limit of 428 rows so it isn't enough anyway. But I can't figure out how to switch the source to CSV, everything I've tried throws errors like 'Comma expected' or 'literal expected'.
This is the code I've been trying (redacted the folder structure), with the CSV as the source:
= Table.AddColumn(#"Changed Type1", "Topic", each Table.AddColumn(#"Changed Type1", "Topics", each let
Lookup = #table({"Topic", "Value"}, {
{"Brand", {"provider", "platform", "my account","supplier" }},
{"Courses/Study", {"tutor", "course", "faculty", "exam", "class", "revision", "tuition", "learn", "learning", "training", "coaching" }},
{"Customer Service", {"service", "student service", "customer", "contact", "support", "response", "conversation", "staff" }},
{"Delivery", {"received", "delivery", "parcel", "shipping", "on time", "delivered", "receipt", "shipped", "prompt", "quickly" }},
{"Ordering/Booking", {"order", "booking", "purchase", "buy", "bought" }},
{"Price/Value for Money", {"money", "price", "cost", "payment", "paid", "£", "value", "fee", "expensive","charge" }},
{"Product/Materials", {"books", "material", "textbook", "ebook", "quality", "resource", "content", "workbook", "text" }},
{"User Experience", {"experience", "easy", "website", "web-site", "helpful", "difficult", "available", "information", "email", "location" }}
}),
Source = Csv.Document(File.Contents("C:\Users\username\Desktop\Folder\Subfolder\reviews_jan20-jul22.csv"),[Delimiter=" ", Columns=6, Encoding=1200]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Review Id", type text}, {"Review Created (UTC)", type datetime}, {"Review Title", type text}, {"Review Content", type text}, {"Review Stars", Int64.Type}, {"Source Of Review", type text}, {"Reference Id", type text}})
in
#"Changed Type"
#"Added Custom" = let lookup = Table.ToRecords(Lookup) in Table.AddColumn(Source, "Topic", each List.Accumulate(lookup, {}, (s,c) => s & {if List.AnyTrue(List.Transform(c[Value], (substring) => Text.Contains([Review Content], substring, Comparer.OrdinalIgnoreCase
))) then c[Topic] else null}))
in
#"Added Custom"
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Topic", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
#"Extracted Values"))
(I'd also like to be able to reference a table of topics and values instead of hardcoding them like this, but that's just a bonus at this point!)
When I input the code, I get "Expression.SyntaxError: Token Comma expected." indicating it's missing above the line
))) then c[Topic] else null}))
but I can't figure out where the comma needs to go, or if that's even what's actually missing.
Can anyone help and explain where I'm going wrong please?
