cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rlama Frequent Visitor
Frequent Visitor

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 from each column of my attached spreadsheet.

 

Please advise.

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to count value from excel spreadsheet

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"

10.png

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
4 REPLIES 4
Pauwnrajpp Member
Member

Re: How to count value from excel spreadsheet

Hi rlama,

 

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

 

YesNo.PNG

 

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 Frequent Visitor
Frequent Visitor

Re: How to count value from excel spreadsheet

Hi Pradeep,

 

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

 

Please help.

Pauwnrajpp Member
Member

Re: How to count value from excel spreadsheet

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

Community Support Team
Community Support Team

Re: How to count value from excel spreadsheet

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"

10.png

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |