Forum Discussion

rlama's avatar
rlama
Regular Visitor
7 years ago
Solved

How to count value from excel spreadsheet

Hi all,   I am new on this forum and trying to learn Power BI. Just want to know how can I count values from my excel spreadsheet to Power BI. I want to count total number of YES, NO and PARTIAL fr...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi rlama ,

     

    You can refer to below steps to add index column and custom column in query editor to get specific text count:

    #"Added Custom1" = Table.AddColumn(#"Added Index", "Yes Count", each List.Count(List.Select(Record.ToList(#"Added Index"{[Index]}),each _="YES")))

     

    Full query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJBDoAgDAT/wtlveCNyMB4M4f/fUKKWdnfxUkSWtju01nSue1rSduTslnKH56DHtpisgOgLXQK3QN2XkYhlOpH/tLKY5j2hjeXSbZskeFc8AMJcCC59XfwzmmP0yiobAaPy6VzdCbUIG5AVzsjEiC6z14+pzMU9QmJjodYPdail5o9eBJsSPYuJmLoXHSFqmqzWLg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t, F = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}, {"F", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Yes Count", each List.Count(List.Select(Record.ToList(#"Added Index"{[Index]}),each _="YES")))
in
    #"Added Custom1"

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi rlama,

 

Make the columns unpivoted in Power BI and find out how many Yes, No and Partial in the value column.

 

 

YES_COUNT : CALCULATE(Count(TableName, Filter(Value = "YES"))

NO_COUNT : CALCULATE(Count(TableName, Filter(Value = "NO"))

PARTIAL_COUNT : CALCULATE(Count(TableName, Filter(Value = "PARTIAL"))

 

Total = YES_COUNT + NO_COUNT + PARTIAL_COUNT

 

Regards,

Pradeep

rlama's avatar
rlama
Regular Visitor
7 years ago

Hi Pradeep,

 

Thanks for the reply. But I have lots of column with values. Don't know how to do it.

 

Please help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi rlama,

     

    With the help of Macro or SSIS Package, you can consolidate all together and push end results(unpivoted) in sql table or Excel. But Excel should support 1048576 Rows in total. So, best choice is to go for SQL table for your end result. 

     

    And, from sql you can do the rest of things in Power BI.

     

    Regards,

    Pradeep