cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Boja Member
Member

Word Count table

Hello guys,

 

I have a problem with creating a summarized table from a list.

PBIDesktop_2018-12-15_15-48-33.png

 

 

The list represents the key words extracted from customers feedback using Azure Cognitive Service. The list is huge. When I try to load it to the model (Close and Apply) it shuts down when it reaches 300 000 rows. So I am trying get an calculated table from it using the Group By function. However, it displays an error (There weren't enough elements in the enumeration to complete the operation).

 

According to posts on the Community it seems that this can be a problem:

Re: Expression.Error: There weren't enough elements in the enumeration to complete the operation

... If your source data don't come in, the subsequent queries will fail as well...

 

Could you please advise me how to get the row count table from the list considering difficulties I am facing?

 

Thank you for your time Smiley Happy

3 REPLIES 3
v-yulgu-msft Super Contributor
Super Contributor

Re: Word Count table

Hi @Boja,

 

Please check if there exosting any error in those applited steps in Query Editor. How did you connect to data source? Did you make any change/transformation to the loaded query?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Boja Member
Member

Re: 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.

PBIDesktop_2018-12-17_10-10-00.png

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.

PBIDesktop_2018-12-17_10-14-06.png   

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"

   

v-yulgu-msft Super Contributor
Super Contributor

Re: Word Count table

Hi @Boja,

 

Sometimes this error is thrown when pivoting columns with same name. Please check if you hit this scenario.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 40 members 858 guests
Please welcome our newest community members: