Syntax to find value

[Stopira]

Hi Expert,

i am new to power BI, i decided to visit forum and ask for solution.

I have two tables.

The first 1 is transactions table

Transaction Date	Transaction Number	Customer Account
2/9/2020	KK-09-0001	KK-00001
10/9/2020	KK-09-0002	KK-00002
27/9/2020	KK-09-0003	KK-00003

The second is SalesLine Table

Transaction Number	Item ID	Item name	Category	Amount
KK-09-0001	Product001	Item A	Product	500,000.00
KK-09-0001	Product002	Bag		-
KK-09-0002	Product003	Item B	Product	200,000.00
KK-09-0002	Product004	Item C	Product	300,000.00
KK-09-0003	Product005	Item D	Product	1,000,000.00

What syntax do i need to use to get the category value to the transaction table. Because if i am using lookupvalue, it will return error.. Just need to get first value..

[Jimmy801 · Accepted solution]

Hello @Stopira 

you can join both tables and transform the joined table (extracting first row of Category). Another approach could be to add a new column where filtering the other table and extract again first row of Category. Here an example code of the first approach

let
    TATable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtK31DcyMDJQ0lHy9tY1sNQ1MDAwhHLAzFidaCVDAyyqjBCqjMCqjMyxqDJGqDJWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Transaction Date" = _t, #"Transaction Number" = _t, #"Customer Account" = _t]),
    SATable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vbWNbDUNTAwMFTSUQooyk8pTS6BcDxLUnMVHBGiQJYCCJgaGOgA1esZGCjF6uAywAjIcUpMB+mB6UMHugoKqAYYIRtgDHOBE6YLjHC4AMUAE5gBzpgGGOMwwBjZAFOYAS5oBhiC9ML1xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Transaction Number" = _t, #"Item ID" = _t, #"Item name" = _t, Category = _t, Amount = _t]),
    JoinTables = Table.NestedJoin
    (
        TATable, 
        "Transaction Number",
        SATable, 
        "Transaction Number",
        "SATable"
    ), 
    TransformJoinedTable = Table.TransformColumns
    (
        JoinTables,
        {
            {
                "SATable",
                (tbl)=> try tbl[Category]{0} otherwise null,
                type text
            }
        }
    )

in
    TransformJoinedTable

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Stopira]

Thank you @Jimmy801