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 blank 

 

Table A 

DateNameCost
14-MayA10
15-MayB11
16-MayC12
16-MayD13

 

 

Table B 

DateNameCost
14-MayA 
15-MayB 
16-MayC 
17-MayD 
   
Need Help

3 Replies

  • 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 

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    How about doing merging Table A into Table B matching on both Date and Name and then expand the Cost column?