cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BI_Analyticz
Helper V
Helper V

How to Fetch the First Values while doing Lookup

I have a situation like below. I want the Status values from Table B for Table A. Common column is Status ID. But in Table B I have 3 values for Status ID = 1.

 

I usually use LookUp --> Lookup(Table B.Name, Table A.Status ID, Table B.Status ID,""). How can I do it if I want all the 3 values from Table B. Yes it obviously makes Table A's one row in to 3 rows. How can we achieve this..?

 

BI_Analyticz_0-1623068467650.png

 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @BI_Analyticz 

If you only want to get first value from each Status ID, you can add an Index column in Power Query.

My Sample:

Table A

1.png

Table B

2.png

Firstly Group all rows in Table B by [Status ID].

3.png

Add  Index M code in Advance Editor:

Indexed = Table.TransformColumns(#"Grouped Rows", {{"Row", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}})

The whole M code is as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvSM1TitWBcJxz8otT4Ty3zIrUFDDPCFmhEUJhLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Status ID" = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Status ID", Int64.Type}, {"Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Status ID"}, {{"Row", each _, type table [Status ID=nullable number, Name=nullable text]}})
    ,Indexed = Table.TransformColumns(#"Grouped Rows", {{"Row", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
    #"Expanded Row" = Table.ExpandTableColumn(Indexed, "Row", {"Name", "GroupIndex"}, {"Row.Name", "Row.GroupIndex"})
in
    #"Expanded Row"

New Table B:

4.png

Merge two tables and filter index =1.

5.png

Result is as below.

6.png

 

Best Regards,

Rico Zhou

 

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

 

 

View solution in original post

6 REPLIES 6
RicoZhou
Community Support
Community Support

Hi @BI_Analyticz 

If you only want to get first value from each Status ID, you can add an Index column in Power Query.

My Sample:

Table A

1.png

Table B

2.png

Firstly Group all rows in Table B by [Status ID].

3.png

Add  Index M code in Advance Editor:

Indexed = Table.TransformColumns(#"Grouped Rows", {{"Row", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}})

The whole M code is as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvSM1TitWBcJxz8otT4Ty3zIrUFDDPCFmhEUJhLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Status ID" = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Status ID", Int64.Type}, {"Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Status ID"}, {{"Row", each _, type table [Status ID=nullable number, Name=nullable text]}})
    ,Indexed = Table.TransformColumns(#"Grouped Rows", {{"Row", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
    #"Expanded Row" = Table.ExpandTableColumn(Indexed, "Row", {"Name", "GroupIndex"}, {"Row.Name", "Row.GroupIndex"})
in
    #"Expanded Row"

New Table B:

4.png

Merge two tables and filter index =1.

5.png

Result is as below.

6.png

 

Best Regards,

Rico Zhou

 

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

 

 

View solution in original post

Pragati11
Super User III
Super User III

Hi @BI_Analyticz ,

 

You will have to create a join between these 2 tables in Power Query Editor. See the blog below:

https://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




@Pragati11  Is there a way to pick the the first value from Table B?

Hi @BI_Analyticz ,

 

To get just the first value, you can use FIRSTNONBLANK dax function. See the details below:

https://docs.microsoft.com/en-us/dax/firstnonblank-function-dax

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




I am unable to use FIRSTNONBLANK in Merge or in Lookup

Hi @BI_Analyticz ,

 

You don't use FIRSTNONBLANK with LOOKUP function.

Check the following thread on how to use this function:

https://community.powerbi.com/t5/Desktop/Lookupvalue-only-first-result/m-p/244009

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors