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 ,

 

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