Mcode: Wrong result using IF statement evaluating Text.StartWith

[Anonymous]

Hello,

I am getting wrong results using if statement evaluating Text.StartWith function can any one guide me on what I am doing wrong?

IF my CategoryValue results starts with "PC" result should be "Yes", but I am getting "No" instead. Inputs?

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Master Data Number", Int64.Type}, {"Description", type text}, {"UniqueValue", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "CategoryValue", each [
currentLookUp = [UniqueValue] ,
a=
Table.Group(lookup, "UniqueValueCol", {"Grouped", each _}){[UniqueValueCol=currentLookUp]}?[Grouped]?{0}?,

b= if a = null then "No Category Value" else a[CategoryValue]][b]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "TextStart", each if Text.StartsWith([CategoryValue],"PC") = "TRUE" then "Yes" else "No")
in
#"Added Custom1"=1&web=1&e=9yLNqx

Sharing my file here 

https://www.dropbox.com/scl/fi/shjlndp1rkx07iqv2d7xi/test_1.xlsx?dl=0&rlkey=5b7rxbkblzhwwdfyr1gjxu3d...

[Anonymous]

@Anonymous no problem! Btw, I was just curious, is there a reason why you preferred not to use the "Merge" functionality to bring data from one table to another? Your approach definitely works but it seems more work to set up and maintain. Cheers.

[Anonymous]

Hi @Anonymous , Yes, I agree. 🙂 For my solution I considered merge however but this solution hits works on requirement.

Thanks,

wbw

[Anonymous · Accepted solution]

You've done pretty much everything right, just update this line of code on query "Table1" as follows:

#"Added Custom1" = Table.AddColumn(#"Added Custom", "TextStart", each if Text.StartsWith([CategoryValue],"PC") = true then "Yes" else "No")

[Anonymous]

Thanks @Anonymous 😁