Word Count table

v-yulgu-msft

Hi Yuliana,

Thanks for quick reply.

I have one table (Data) containing customer feedback. This table is connected to Azure Cognitive Service which extracts key phrases into the new column KeyPhrases.

Then i referenced the table Data, removing all fields except he KeyPhrases column (this way i created a single column table KeywordList). Then I performed following steps on the column to get single words from phrases. Please find photos that expains more. The result is the single column table with the list of single words showed in the first post.

let
    Source = Data,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"KeyPhrases"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "KeyPhrases", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"KeyPhrases.1", "KeyPhrases.2", "KeyPhrases.3", "KeyPhrases.4", "KeyPhrases.5", "KeyPhrases.6", "KeyPhrases.7", "KeyPhrases.8", "KeyPhrases.9", "KeyPhrases.10", "KeyPhrases.11", "KeyPhrases.12", "KeyPhrases.13", "KeyPhrases.14", "KeyPhrases.15", "KeyPhrases.16", "KeyPhrases.17", "KeyPhrases.18", "KeyPhrases.19"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"KeyPhrases.1", type text}, {"KeyPhrases.2", type text}, {"KeyPhrases.3", type text}, {"KeyPhrases.4", type text}, {"KeyPhrases.5", type text}, {"KeyPhrases.6", type text}, {"KeyPhrases.7", type text}, {"KeyPhrases.8", type text}, {"KeyPhrases.9", type text}, {"KeyPhrases.10", type text}, {"KeyPhrases.11", type text}, {"KeyPhrases.12", type text}, {"KeyPhrases.13", type text}, {"KeyPhrases.14", type text}, {"KeyPhrases.15", type text}, {"KeyPhrases.16", type text}, {"KeyPhrases.17", type text}, {"KeyPhrases.18", type text}, {"KeyPhrases.19", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "")),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows",{{"Value", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Value", "KeyWord"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Grouped Rows" = Table.Group(#"Removed Blank Rows", {"KeyWord"}, {{"Count", each Table.RowCount(_), type number}})

in
    #"Grouped Rows"