How to count value from excel spreadsheet

[rlama]

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.

[v-shex-msft · Accepted solution]

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]

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]

Hi Pradeep,

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

Please help.

[Anonymous]

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