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 Team,
Can anyone help me resolve this issue.
This is the sample data set
computer name | Status |
A | Software Installed |
B | Software not installed |
B | Service not running |
C | Service not running |
D | Software Installed |
Expected outcome
Computer name | Software Installed | Software NotInstalled | Service Not running |
A | 1 | 0 | 0 |
B | 0 | 1 | 1 |
C | 0 | 0 | 1 |
D | 1 | 0 | 0 |
I have tried grouping the column and then pivot. Added index column, But still getting an error "OLE DB or ODBC error: [Expression.Error] We cannot convert the value true to type Text.." and in the power query when i look into the error it says "Too many elements in the enumeration to complete"
Hi @Dsingh ,
You can follow these steps to try:
1. Use group by based on 'computer name':
2. Add a custom column using this formula:
=let
B = [Data],
#"Removed Other Columns" = Table.SelectColumns(B,{"Status"}),
#"Grouped Rows1" = Table.Group(#"Removed Other Columns", {"Status"}, {{"Count", each Table.RowCount(_), type number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows1", List.Distinct(#"Grouped Rows1"[Status]), "Status", "Count", List.Sum)
in
#"Pivoted Column"
3. Remove 'Data' column and expand 'Custom' column
4. Replace values from null to 0 to get the expected result:
My sample file is attached that hopes to help you, please check and try it: Cannot Convert true to text.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much. It worked!!
Please see the below . Getting this error after pivoting
Hi @Dsingh ,
Apart from the solution mentioned,
You can simply put it in a matrix visual.
Rows = Computer Name
Column = Status
Value = Count of Status
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
@Dsingh ,
Take a measure count(Table[Status]) +0
Hi , Thanks for responding! But i would need the expected outcome in the power query.
@Dsingh , I doubt you can get without having a numeric column using the unpivot option give below
https://radacad.com/pivot-and-unpivot-with-power-bi
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |