Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
Hi Pradeep,
Thanks for the reply. But I have lots of column with values. Don't know how to do it.
Please help.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |