Text.PositionOf not working

[PeteIannetta]

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

[RiemuRiku]

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.

[v-kelly-msft]

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!

[Fowmy]

@PeteIannetta 

Works for me as well, can you re-check if the "_" is not represented as double "__" if you had any other transformation steps

[PeteIannetta]

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

[ImkeF]

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.

[PeteIannetta]

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?

[Fowmy]

@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...

[ImkeF]

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.