Forum Discussion
IF Test Starts With in Power Query
Hi all how do i use the Text.StartsWith in Power Query in this scenario if i had a column saying something like London Region or London Area etc but i just wanted to say if coln starts with london then....
What would be the correct formula ?
Hi Jay2022,
Thank you for reaching out to Microsoft Fabric Community.
Based on your requirement, please follow below steps:
Open Power Query Editor, go to Add Column --> Custom Column. Name the column and in the formula box, paste this below code and click OK.
if Text.StartsWith([ColumnName], "London", Comparer.OrdinalIgnoreCase) then "Yes" else "No"
Replace ColumnName with the actual column name. This will create a new column with Yes if the row starts with London or else No.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi Jay2022
In Power Query, if you want to filter or create a condition based on whether a column value starts with the word "London"—such as "London Region", "London Area", or any other variation beginning with "London"—you can use the `Text.StartsWith` function. This function checks whether a given text string begins with a specified substring. For example, you could add a custom column using a formula like `Text.StartsWith([ColumnName], "London")`, which will return `true` for any value in the column that begins with "London", and `false` otherwise. You can then use this boolean result to filter your rows or drive further conditional logic. Make sure the column you're applying this to is of type `text`; if not, you should first convert it using `Text.From([ColumnName])`. This approach is especially useful when you're working with regional or descriptive data and want to isolate rows that refer to a specific city or prefix regardless of the full value.
Hi Jay2022,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Thanks and regards,
Anjan Kumar Chippa
8 Replies
The third parameter of the Text.StartsWith function can specify the comparison method. You can specify it as "Comparer.OrdinalIgnoreCase" to ignore case.
Table.AddColumn(Table.FromValue({"London A", "lonDON"}), "NEW", each Text.StartsWith([Value], "london", Comparer.OrdinalIgnoreCase))Hi Jay2022 This checks if the column starts with "London" and adds a "Yes" or "No" accordingly.
= Table.AddColumn(Source, "London Check", each if Text.StartsWith([ColumnName], "London") then "Yes" else "No")
Thank you i'm still not sure what to put in the custom column field i assume i don't start with Table.AddColumn
Hi Jay2022,
Thank you for reaching out to Microsoft Fabric Community.
Based on your requirement, please follow below steps:
Open Power Query Editor, go to Add Column --> Custom Column. Name the column and in the formula box, paste this below code and click OK.
if Text.StartsWith([ColumnName], "London", Comparer.OrdinalIgnoreCase) then "Yes" else "No"
Replace ColumnName with the actual column name. This will create a new column with Yes if the row starts with London or else No.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi Jay2022,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Thanks and regards,
Anjan Kumar Chippa
Hi Jay2022
In Power Query, if you want to filter or create a condition based on whether a column value starts with the word "London"—such as "London Region", "London Area", or any other variation beginning with "London"—you can use the `Text.StartsWith` function. This function checks whether a given text string begins with a specified substring. For example, you could add a custom column using a formula like `Text.StartsWith([ColumnName], "London")`, which will return `true` for any value in the column that begins with "London", and `false` otherwise. You can then use this boolean result to filter your rows or drive further conditional logic. Make sure the column you're applying this to is of type `text`; if not, you should first convert it using `Text.From([ColumnName])`. This approach is especially useful when you're working with regional or descriptive data and want to isolate rows that refer to a specific city or prefix regardless of the full value.
