Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PeteIannetta
Regular Visitor

Text.PositionOf not working

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

 

8 REPLIES 8
RiemuRiku
Frequent Visitor

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
Community Support
Community Support

Hi @PeteIannetta ,

 

Really strange,as tested here,it works fine:

vkellymsft_0-1634012703407.pngvkellymsft_1-1634012747304.png

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
Super User
Super User

@PeteIannetta 

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

Fowmy_0-1633781957543.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


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

ImkeF
Super User
Super User

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




Did I answer your question? Mark my post as a solution! and hit thumbs up


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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors