Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Date | Name | Cost |
14-May | A | 10 |
15-May | B | 11 |
16-May | C | 12 |
16-May | D | 13 |
Table B
Date | Name | Cost |
14-May | A | |
15-May | B | |
16-May | C | |
17-May | D | |
How about doing merging Table A into Table B matching on both Date and Name and then expand the Cost column?
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"
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.