Lookup operation in power query

Power Query
1

How to perform a lookup operation in power query?

2

Hi @Anu,

Please provide more context and an example.
Thanks.

3

sample 11_4.pbix (45.9 KB)

Please find the attached file.

I would like to implement this logic in power query:

Cashflow Category_ES_CP =
VAR lookup =
LOOKUPVALUE (
‘Lookup Table 1’[Value],
‘Lookup Table 1’[Location Code], ‘Receivables’[Location Code]
)
VAR Category =
IF (
lookup = “100”,
“Cash from Existing Sales”,
IF (
lookup = “200”,
“Cash from inventory sales”,
IF ( lookup = “300”, “Cash from new launches”, “Others” )
)
)
RETURN
IF (
‘Receivables’[Cashflow Category] = “Cash from Existing Sales”
|| ‘Receivables’[Cashflow Category] = “Cash from completed projects”,
Category,
LOOKUPVALUE (
‘Lookup Table 2’[Category],
‘Lookup Table 2’[Location Code], ‘Receivables’[Location Code]
)
)

4

Hi @Anu,

There are many ways to look things up in M, but give this a go.

AddCategory = Table.AddColumn(#"Changed Type", "Custom", each 
    let
        lookup = BufferLookup1{[Location Code = [Location Code]]}?, 
        Cat1 = if lookup <> null then
        (
            if lookup[Value] = "100" then "Cash from Existing Sales"
            else if lookup[Value] = "200" then "Cash from inventory sales"
            else if lookup[Value] = "300" then "Cash from new launches"
            else "Others"
        )
        else null, 
        Cat2 = try BufferLookup2{[Location Code = [Location Code]]}[Category] otherwise null
    in
        if [Category] = "Cash from Existing Sales" or [Category] = "Cash from completed projects" then Cat1
        else Cat2, 
    type text
)

the result.
image

Here’s your sample file. sample 11_4.pbix (47.8 KB)
I hope this is helpful.

3 Likes
5

BufferLookup1{[Location Code = [Location Code]]}?

Cat2 = try BufferLookup2{[Location Code = [Location Code]]}[Category] otherwise null

could you please explain this part a little?

6 
AddCategory = Table.AddColumn(#"Changed Type", "Custom", each 
  let
    lookup = BufferLookup1{[Location Code = [Location Code]]}?, // get matching Record else null
    Cat1 = if lookup <> null then // if not null (so there is a matching Record)
      (
        if lookup[Value] = "100" then "Cash from Existing Sales"
        else if lookup[Value] = "200" then "Cash from inventory sales"
        else if lookup[Value] = "300" then "Cash from new launches"
        else "Others"
      )
      else null, 
    Cat2 = try BufferLookup2{[Location Code = [Location Code]]}[Category] otherwise null // get matching Record field value otherwise null
  in
    if [Category] = "Cash from Existing Sales" or [Category] = "Cash from completed projects" then Cat1
      else Cat2, 
  type text
)
1 Like
8

try BufferLookup2 , could you please explain “try”

9

@Anu You should google and try to find what a certain function does, Melissa won’t be able answer everything.

Few days ago I saw her writing “?” after a line of code and instead of asking her I googled and found articles/answers and video that explained that it is used to prevent errors and return null.

That’s how you will learn.

4 Likes
10

Hi @Anu, did the response provided by the users and experts help you solve your query?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

11

Hi @Anu, we’ve noticed that no response has been received from you since the 11th of April. We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

12

Hi @Anu, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!