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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
halifaxious
Frequent Visitor

explain this custom function, please?

I found a function to replace text using a lookup table. It works great. But the syntax is unfamiliar to me and I can't find a reference for it.  Here's what I have:

 

replacer = (x,y,z)=>LookupTable{[#"lookup value"=x]}?[replacement]? ??x

 

I have 3 questions about the above:

  1. why does the =x go inside the column reference? LookupTable{[#"lookup value"=x]}
  2. where is the documentation for what looks like a ternary operator in M?  a?b? ??c
  3. why doesn't the column reference in the true portion of the statement need to be fully justified with the table name? [replacement]

 

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

Hi @halifaxious,

 

There is a lot going on here

 

 

replacer = (x,y,z)=>LookupTable{[#"lookup value"=x]}?[replacement]? ??x

 

 

 

First this is a custom replacer function which takes 3 arguments: 

x = original value

y = match condition (true or false)

z = replacement value

 

Next its applying (optional) key match lookup to identify a unique row in the table

LookupTable{ [#"lookup value"=x] }?

 

Followed by (optional) field access, to return a value from a field/ column

[replacement]?

 

And finally applying coalesce, to return the input value if a null is returned.

?? x

 

Ps. If this helps solve your query please mark this post as Solution, thanks!

 

Some links to resources on these topics: 

Visual guide to key match lookup 

Visual guide to coalesce 

Item- and Field access 

 

View solution in original post

2 REPLIES 2
m_dekorte
Super User
Super User

Hi @halifaxious,

 

There is a lot going on here

 

 

replacer = (x,y,z)=>LookupTable{[#"lookup value"=x]}?[replacement]? ??x

 

 

 

First this is a custom replacer function which takes 3 arguments: 

x = original value

y = match condition (true or false)

z = replacement value

 

Next its applying (optional) key match lookup to identify a unique row in the table

LookupTable{ [#"lookup value"=x] }?

 

Followed by (optional) field access, to return a value from a field/ column

[replacement]?

 

And finally applying coalesce, to return the input value if a null is returned.

?? x

 

Ps. If this helps solve your query please mark this post as Solution, thanks!

 

Some links to resources on these topics: 

Visual guide to key match lookup 

Visual guide to coalesce 

Item- and Field access 

 

Thank you for a very illuminating answer! I wish I'd come here about 2 hours ago instead of fruitlessly googling.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors