Forum Discussion
Cannot Convert true to text
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"
7 Replies
Hi Anonymous ,
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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous ,
Take a measure count(Table[Status]) +0
Anonymous , I doubt you can get without having a numeric column using the unpivot option give below
