Forum Discussion

ag00559054's avatar
ag00559054
Frequent Visitor
4 years ago

Use IF and return a lookupvalue

I have 2 tables ..and would want to check IF the dates are same in both tables then return cost from Table A to corresonding Table B if the cost is not included in table A leave the table B also blan...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Use below formula in a custom column

= try Table.SelectRows(#"Table A", (x)=> x[Date]=[Date] and x[Name]=[Name]){0}[Cost] otherwise null

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTR9U2sVNJRclSK1QFyTaFcJwjXDMp1hnDNoVwXpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try Table.SelectRows(#"Table A", (x)=> x[Date]=[Date] and x[Name]=[Name]){0}[Cost] otherwise null)
in
    #"Added Custom"

 

  • ag00559054's avatar
    ag00559054
    Frequent Visitor
    4 years ago

    Vijay_A_Verma  Sorry ..i am new to power BI..Hence not able to word my queries correctly ....I have uploaded 2 file folders ..Folder 1 - Table A has daily data from Apr (30 -40 Excel files) and Folder 2 : Table B similarly  30-40  ..Both folders have different data..The only common data between them  is the Date ,name ...I want to check if the dates from Table A & Table B are same then retrieve  the cost from Table B to Table A

     

    i used 

    LOOKUPVALUE('Table A'[Cost],'Table A'[Name],Table B[Name]) 

    But this will give me an error since i am not checking if the dates match..thats why i was thinking if i can use IF