IF Test Starts With in Power Query

[Jay2022]

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 ?

[v-achippa · Accepted solution]

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

[Poojara_D12 · Accepted solution]

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.

[Poojara_D12 · Accepted solution]

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.

[Jay2022]

Thank you i'm still not sure what to put in the custom column field i assume i don't start with Table.AddColumn 

[v-achippa · Accepted solution]

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

[v-achippa · Accepted solution]

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

[ZhangKun]

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

[Akash_Varuna]

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")

[Jay2022]

Thank you for this/how and where do I enter this ?

[pbiuseruk]

Hello,

Go into Transform Query, then into the specific Query or dataset that you want to apply the logic to.

Then go to the Add Column tab and then select Custom Column. Put the code in there and it should work.