Forum Discussion
Error on Merge
Hi all,
I am trying to merge a total table to a daily table to get the total amount which I can then multiply by a daily factor to spread revenue by day and then summarize by the appropriate month.
I have an the id field in each tabl as text
The id is 18 characters long and follows this pattern: a0ERk000001GJ1JMAW
But when I try to Merge and expand for the various fields I am interested in I keep getting this error:
An error occurred in the ‘Rec Include’ query. DataFormat.Error: We couldn't convert to Number.
Details:
None
I checked all id's, they are all a mix of alphanumeric
Value.Is(Value.FromText([ID]), type number))
Any ideas?
Thanks,
-w
Thanks for the reply fromAshish_Mathur and danextian , please allow me to provide another insight:
Hi, tecumseh
Thank you for your detailed description of your problem, and we see your efforts.
According to the information you provided, the main reason is that the data type conversion fails because your M language involves the steps of converting other types of data into digital types.
1.In my measurements, converting data of the number type to the text type does not report an error:
But the conversion of text types to numeric types is not necessarily:
2.So based on your situation, it is recommended to check the part of your M language that converts other types of data into numeric types, and delete or modify them as needed.
3.Of course, if you want to convert a text type to a numeric type and check if there is a text type in the column, here's how to change it according to the code you provided:
Value.Is(Value.FromText([ID]), type text)
powerquery - Is there an ISNUMBER() or ISTEXT() equivalent for Power Query? - Stack Overflow
4.Here is a link to a post with a similar issue to yours, I hope it will be helpful to you:
Solved: DataFormat.Error: We couldn't convert to Number. - Microsoft Fabric Community
Solved: Merged Queries - ERROR - Microsoft Fabric Community
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi tecumseh ,
It is possible that in Rec Include query one of the columns is formatted to number but a row or more actually contains a text string. Power Query automatically detects the data type based on the first 1000 rows only so somewhere beyond that, there's a row that cannot be converted to a number.
Thanks for the reply fromAshish_Mathur and danextian , please allow me to provide another insight:
Hi, tecumseh
Thank you for your detailed description of your problem, and we see your efforts.
According to the information you provided, the main reason is that the data type conversion fails because your M language involves the steps of converting other types of data into digital types.
1.In my measurements, converting data of the number type to the text type does not report an error:
But the conversion of text types to numeric types is not necessarily:
2.So based on your situation, it is recommended to check the part of your M language that converts other types of data into numeric types, and delete or modify them as needed.
3.Of course, if you want to convert a text type to a numeric type and check if there is a text type in the column, here's how to change it according to the code you provided:
Value.Is(Value.FromText([ID]), type text)
powerquery - Is there an ISNUMBER() or ISTEXT() equivalent for Power Query? - Stack Overflow
4.Here is a link to a post with a similar issue to yours, I hope it will be helpful to you:
Solved: DataFormat.Error: We couldn't convert to Number. - Microsoft Fabric Community
Solved: Merged Queries - ERROR - Microsoft Fabric Community
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share the download link of the PBI file and show the problem there clearly.
