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.
Hello, I have a table with various columns to include: name, country1, country2, country3 and so forth
Each county1-countryX can have several values from a fixed defined choice. Example: Pass, Fail, N/A, etc
I want to produce a report for each name that counts the number of this values for each row across the columns
Below is an example, with columns CountPass & Count Fail countiing for each row across columns
Name, C1,C2,C3,C4, CountPass, CountFail
A,Pass,Pass, Fail, Fail, 2, 2
B Pass, Fail, Fail, Fail,1,3
C Fail,Fail,Fail,Fail, 0,4
Someone in the forum posted to simply add a coumn for each result
List.Count(List.Select(Record.FieldValues(_), (x) => Text.Contains(x, "PASS")))
List.Count(List.Select(Record.FieldValues(_), (x) => Text.Contains(x, "Fail")))
I have tried this but the column gives a value of "ERROR"
Solved! Go to Solution.
you can try to use DAX to create columns, However, if the number of country is large , that will be complicated.
CountPass =
VAR c1=if('Table'[C1]="Pass",1,0)
VAR c2=if('Table'[C2]="Pass",1,0)
VAR c3=if('Table'[C3]="Pass",1,0)
VAR c4=if('Table'[C4]="Pass",1,0)
RETURN c1+c2+c3+c4
CountFail =
VAR c1=if('Table'[C1]="Fail",1,0)
VAR c2=if('Table'[C2]="Fail",1,0)
VAR c3=if('Table'[C3]="Fail",1,0)
VAR c4=if('Table'[C4]="Fail",1,0)
RETURN c1+c2+c3+c4
Proud to be a Super User!
@manojs although I would recommend to unpivot your table but the code you posted is not working because when you add the second custom column, it sees the first custom column you added as a number because the type of that column by default will be any
There are two ways to handle it, either change type of column to text after your add first custom column and then add a second custom column or change expression as below
List.Count(List.Select(Record.FieldValues(_),(x)=>Text.Contains(Text.From(x), "Fail"))))
Again, as a best practice, unpivot should be your approach.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @manojs ,
You could refer to above suggestions, you also could refer to my M code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpILC5GUG6JmTkwKlYnWslJAbsckhJn7FIwFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #" C1" = _t, C2 = _t, C3 = _t, C4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {" C1", type text}, {"C2", type text}, {"C3", type text}, {"C4", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Name", "Value"}, {{"Count", each Table.RowCount(_), type number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Value]), "Value", "Count", List.Sum),
#"Merged Queries" = Table.NestedJoin(#"Pivoted Column", {"Name"}, #"Changed Type", {"Name"}, "Pivoted Column", JoinKind.LeftOuter),
#"Expanded Pivoted Column" = Table.ExpandTableColumn(#"Merged Queries", "Pivoted Column", {" C1", "C2", "C3", "C4"}, {" C1", "C2", "C3", "C4"})
in
#"Expanded Pivoted Column"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @manojs ,
You could refer to above suggestions, you also could refer to my M code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpILC5GUG6JmTkwKlYnWslJAbsckhJn7FIwFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #" C1" = _t, C2 = _t, C3 = _t, C4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {" C1", type text}, {"C2", type text}, {"C3", type text}, {"C4", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Name", "Value"}, {{"Count", each Table.RowCount(_), type number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Value]), "Value", "Count", List.Sum),
#"Merged Queries" = Table.NestedJoin(#"Pivoted Column", {"Name"}, #"Changed Type", {"Name"}, "Pivoted Column", JoinKind.LeftOuter),
#"Expanded Pivoted Column" = Table.ExpandTableColumn(#"Merged Queries", "Pivoted Column", {" C1", "C2", "C3", "C4"}, {" C1", "C2", "C3", "C4"})
in
#"Expanded Pivoted Column"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
you can try to use DAX to create columns, However, if the number of country is large , that will be complicated.
CountPass =
VAR c1=if('Table'[C1]="Pass",1,0)
VAR c2=if('Table'[C2]="Pass",1,0)
VAR c3=if('Table'[C3]="Pass",1,0)
VAR c4=if('Table'[C4]="Pass",1,0)
RETURN c1+c2+c3+c4
CountFail =
VAR c1=if('Table'[C1]="Fail",1,0)
VAR c2=if('Table'[C2]="Fail",1,0)
VAR c3=if('Table'[C3]="Fail",1,0)
VAR c4=if('Table'[C4]="Fail",1,0)
RETURN c1+c2+c3+c4
Proud to be a Super User!
Thx Ryan,
This is good but would mean that it would rely on hard coding the column names, I was looking for an automated way and hence the two line examples in my original post: though to get any information about that constructed that was posted is almost impossible on searching in google - looks like magic
@manojs although I would recommend to unpivot your table but the code you posted is not working because when you add the second custom column, it sees the first custom column you added as a number because the type of that column by default will be any
There are two ways to handle it, either change type of column to text after your add first custom column and then add a second custom column or change expression as below
List.Count(List.Select(Record.FieldValues(_),(x)=>Text.Contains(Text.From(x), "Fail"))))
Again, as a best practice, unpivot should be your approach.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@manojs I would recommend unpivoting your table, and then everything will be much easier, not sure if you are adding countpass and count fail columns, here are the steps
- transform data
- remove countpass and countfail columns (if these are part of the table)
- select name column
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply
To visualize,
- matrix visual:
- add name on rows,
- add attribute on columns
- add value on values section and the aggregation for this will be count, you can also create measures but let's starts with this.
The above will get you the count you are looking for and this table transformation is following the best practice and can be easily used for other analyses.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |