Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey Everyone,
Hope this message finds you safe and well. On one of my report, I need to add a dynamic target line to a line chart. So I entered the target data as new table region wise. The idea is to join this target table with a main table based on region and the target line of the visual would change with the selection of a region slicer from the main table. But these numbers are of text data type by default and I get errors (dataformat.error we couldn't convert to number) when i try to change the data type to numbers. Any advice on how I should go about this?
Thank you in advance.
Best,
Akash
Solved! Go to Solution.
Thank you so much everyone for all the help . As this was a small table, I tried recreating the issue again by manually entering the data and this time it read as whole number. I was able to make the joins and it works perfectly:)
Hi @Anonymous ,
Do the suggestions from engineers make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best regards
Amy
Thank you so much everyone for all the help . As this was a small table, I tried recreating the issue again by manually entering the data and this time it read as whole number. I was able to make the joins and it works perfectly:)
Hi @Anonymous ,
For this errors "dataformat.error we couldn't convert to number", you may enter into Query Editor, select on this query, go to APPLIED STEPS, find the step named "Changed type" (maybe in the 2nd, 3rd step), then you can do the data type transform which converts the data type from text to number on this step, make sure click "Replace Current" in pop up page.
If you need to unpivot the 4 columns to combined column, you may remove the relationship first of all, then go to Query Editor to unpivot the 4 columns. After that, you may recreate Many to Many relationship between the two tables.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous - Odd, maybe try Clean and Trim operations on the columns? Also, I would unpivot those quarter columns.
Hey @Greg_Deckler ,
Thanks for your quick reply. I am unable to unpivot those fields like the image below, i get an error when i try to apply changes saying :
But this is not the main problem, I have tried trimming and cleaning the columns, even after unpivoting in the query editor, I get errors while coverting to numbers.
When i try to change data type in pbi desktop, it asks me this and i get erros if i click yes.
@Anonymous Remove any relationships to that table. Unpivot. Create a bridge table to bridge that table to the other table that was related to it. Did the clean and trim allow you to convert it to a number?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |