Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a column of text at my work that contains questions. In these fields input anything can be typed. I want to better understand the sentiment of the words used so return a count for each unique word in the column. How can I acheive this?
If that is too hard is it possible to supply a list of 20-30 words and have the counts of those words in the columns returned?
Due to IT policy I cannot paste my work data but have supplied this dummy data as a basis.
Date Text 1/01/2017 Lorem ipsum dolor sit amet, consectetur adipiscing elit. 1/01/2017 Itaque contra est, ac dicitis; 2/01/2017 Beatus sibi videtur esse moriens 3/01/2017 naturam affectum esse possit Beatus 2/01/2017 moriens. Quo igitur, inquit, modo?
Solved! Go to Solution.
Try this (used an Enter Data query to start with)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY6xDsIwDAV/xcpctRQGBgYkNiQW5qpDSNzKUhOX2OH7SQtDEfvdvdd1pm12bbPftUdTmRsnDECz5ACeJ04gpGADagWOo6BT1JzAeppJHMURcCKtTV/9hq5qnxkXR5MFlOJbB54cKclpxfcb/IJWs5SxB8GL/LqBIgiBE2GUVThshFj4ZAPYYSifytuVnlmWv5/a38i3VcM9M9BIpVABxWem8i6w57Pp+zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Text", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Text", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Text.1", "Text.2", "Text.3", "Text.4", "Text.5", "Text.6", "Text.7", "Text.8"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}, {"Text.3", type text}, {"Text.4", type text}, {"Text.5", type text}, {"Text.6", type text}, {"Text.7", type text}, {"Text.8", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}) in #"Removed Columns"
and this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY6xDsIwDAV/xcpctRQGBgYkNiQW5qpDSNzKUhOX2OH7SQtDEfvdvdd1pm12bbPftUdTmRsnDECz5ACeJ04gpGADagWOo6BT1JzAeppJHMURcCKtTV/9hq5qnxkXR5MFlOJbB54cKclpxfcb/IJWs5SxB8GL/LqBIgiBE2GUVThshFj4ZAPYYSifytuVnlmWv5/a38i3VcM9M9BIpVABxWem8i6w57Pp+zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Text", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Text", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Text.1", "Text.2", "Text.3", "Text.4", "Text.5", "Text.6", "Text.7", "Text.8"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}, {"Text.3", type text}, {"Text.4", type text}, {"Text.5", type text}, {"Text.6", type text}, {"Text.7", type text}, {"Text.8", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Value"}) in #"Removed Duplicates"
Relate the two tables on Value and then you should be able to create a matrix and get your counts.
Try this (used an Enter Data query to start with)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY6xDsIwDAV/xcpctRQGBgYkNiQW5qpDSNzKUhOX2OH7SQtDEfvdvdd1pm12bbPftUdTmRsnDECz5ACeJ04gpGADagWOo6BT1JzAeppJHMURcCKtTV/9hq5qnxkXR5MFlOJbB54cKclpxfcb/IJWs5SxB8GL/LqBIgiBE2GUVThshFj4ZAPYYSifytuVnlmWv5/a38i3VcM9M9BIpVABxWem8i6w57Pp+zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Text", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Text", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Text.1", "Text.2", "Text.3", "Text.4", "Text.5", "Text.6", "Text.7", "Text.8"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}, {"Text.3", type text}, {"Text.4", type text}, {"Text.5", type text}, {"Text.6", type text}, {"Text.7", type text}, {"Text.8", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}) in #"Removed Columns"
and this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY6xDsIwDAV/xcpctRQGBgYkNiQW5qpDSNzKUhOX2OH7SQtDEfvdvdd1pm12bbPftUdTmRsnDECz5ACeJ04gpGADagWOo6BT1JzAeppJHMURcCKtTV/9hq5qnxkXR5MFlOJbB54cKclpxfcb/IJWs5SxB8GL/LqBIgiBE2GUVThshFj4ZAPYYSifytuVnlmWv5/a38i3VcM9M9BIpVABxWem8i6w57Pp+zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Text", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Text", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Text.1", "Text.2", "Text.3", "Text.4", "Text.5", "Text.6", "Text.7", "Text.8"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}, {"Text.3", type text}, {"Text.4", type text}, {"Text.5", type text}, {"Text.6", type text}, {"Text.7", type text}, {"Text.8", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Value"}) in #"Removed Duplicates"
Relate the two tables on Value and then you should be able to create a matrix and get your counts.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |