When index is -1 return X

[JamesH2405]

hi all,

I am very new to all of this so please excuse my lack of knoweldge, i am just starting out learning Bi and DAX etc.

i was self taught on excel and created a formula to lookup from a list then if the value doesnt exist to insert "X"

now, ive worked out how to index in power query and that if the value doesnt exist it returns -1, but how do i get the lookup value to insert "X" if the index is -1?

Excel Formula 

=IF(OR(E2=""),"LOCAL",IF(ISNA(VLOOKUP(E2,Sheet1!$A$2:$B$65,2,0)),"LOCAL",VLOOKUP(E2,Sheet1!$A$2:$B$65,2,0)))

DAX

= Table.AddColumn(#"Changed Type3", "Customer", each Customer[Name]{List.PositionOf(Customer[Customer Number],[Customer number])})

any help would be amazing 🙂

[Anonymous · Accepted solution]

Hi @JamesH2405 ,

Click "Advanced Editor" to copy and paste the following code, and check the steps on the right.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUpMUorViVZyAjJTU8BMkGhaCly0OBHMdAGpLQYznYHMlFS4KFStG1RBLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerName = _t, Other = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerName", type text}, {"Other", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"CustomerName"}, Table1, {"CustomerName"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"CustomerID"}, {"Table1.CustomerID"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Table1", "Custom", each if [Table1.CustomerID] = null then "Local" else if [Table1.CustomerID] <> null then [Table1.CustomerID] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Table1.CustomerID"})
in
    #"Removed Columns"

Or you can also use the LOOKUPVALUE() function in desktop.

Measure = 
var _a=LOOKUPVALUE('Table'[value],'Table'[type],MAX('Table (2)'[type]))
return IF(_a=BLANK(),"Local",_a)

Please refer to the following documents for more information.

How to Perform VLOOKUP in Power Query in Excel - A Step by Step Guide (excelchamps.com)

DAX In Power BI - How To Use LOOKUPVALUE() Function (c-sharpcorner.com)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[JamesH2405]

thats amazing, just what i needed, 

im still very new to all of this and self learning so its all very fresh 🙂