How to Fetch the First Values while doing Lookup

[BI_Analyticz]

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..?

[Anonymous · Accepted solution]

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

Table B

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

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:

Merge two tables and filter index =1.

Result is as below.

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. 

[Pragati11]

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

[BI_Analyticz]

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

[Pragati11]

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

[BI_Analyticz]

I am unable to use FIRSTNONBLANK in Merge or in Lookup

[Pragati11]

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