Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi, Up until the 3/9/21 I had a query that used the Text.PositionOf function to return the position of an underscore in a text field.
Since then, it's generating an error stating that an invalid length parameter was passed to this function.
The field has not changed nor has the query. The field is confirmed as a string type, and I can use the Text.Contains function to successfully return a TRUE value when looking for the underscore in the text field. So the underscopre is there, but the PostiionOf function for some reason is returning a -1 value, indicating that there is no underscore.
An example of the text field I'm searching:
CAT0046_SCAT0295
This is the code that is not working and is returning -1:
Text.PositionOf([Task Full Code],"_")
This is the code that is working and returning a TRUE value:
Text.Contains([Task Full Code],"_")
I cannot troubleshoot what is happening here and why it's suddenly stopped but any tips from an expert better than me would be greatly appreciated.
Cheers,
Pete
For me, it ended up to being an issue of source column being in the wrong datatype.
PowerBI incorrectly showed the datatype of source column being text.
For some reason, using source datatype, or
duplicating the source datatype and typing the data in the same step will not work.
Got it to work by duplicating source column and reassigning the text datatype to it in a separate step.
If i would have to guess, this could be caused by source data codepages <--> query optimization...?
Leaving this here, if someone happens to run across the same issue in the future.
Hi @PeteIannetta ,
Really strange,as tested here,it works fine:
Below is my M codes:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOf([Column1],"_")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2"})
in
#"Removed Columns"
Would you pls check if you change the column type to text,whether the issue still exists?
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@PeteIannetta
Works for me as well, can you re-check if the "_" is not represented as double "__" if you had any other transformation steps
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks for the reply, checked that, definitely a single udnerscore. As mentioned, the query code has not changed and has just stopped working, but yes, it's a single _ only, thanks for replying
Hi @PeteIannetta ,
that looks like a bug to me and I would recommend to open a ticket.
With the sample you've given, I cannot reproduce the error, returning 7 for me.
So whatever transactions until this step happen might have created a special circumstance that leads to this "unlogical" behaviour.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks Imke, the strange thing is that when I do a test of the functions, they show -1 whilst in PowerQuery in Excel, but when I load into Excel, it shows 7, so it seems to be working, I just can't get it to that point. I've had a look online, can't find where to log a Power Query support ticket, do you happen to have the link please?
@PeteIannetta
It comes all under Office 365 Support. Following link should help open a ticket:
https://support.microsoft.com/en-us/topic/contact-microsoft-office-support-fd6bb40e-75b7-6f43-d6f9-c...
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @PeteIannetta ,
I thought it was Power BI. Have you checked the code there?
Unfortunately I don't know if (and how) it is possible to open a ticket for Excel.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
56 | |
43 | |
28 | |
22 |