Forum Discussion
How to return/bring values from multiple data table based on lookup criteria as single column
I have muliple data files say 2, 3, 4 (every sheet have different data structure thus cannot appended)
File 2
| CODE | AMOUNT |
| 1114101 | 31,500 |
| 1114101 | 59,810 |
| 1114101 | 90,000 |
| 1114101 | 3,960 |
| 1114101 | 18,816 |
File 3
| Code | Amount |
| 2112201 | 199,457 |
| 2112201 | (4,171,420) |
| 2112201 | 93,450 |
| 2112201 | 312,323 |
| 2112201 | 14,238 |
| 2112201 | 378,548 |
| 2112201 | 159,327 |
File 4
| Code | Total Due |
| 2111101 | 381 |
| 2111101 | 150 |
| 2111101 | 19,286 |
| 2111101 | 0 |
| 2111101 | 1,690 |
| 2111601 | 0 |
| 2111601 | (25) |
but a common reference number/code available in file 1 to lookup the values from 2,3,4.
| Reference | Description | Ending Balance | New Vaues required |
| 1113104 | PMBC | 72,000.00 | |
| 1114101 | FAF | 19,343,966.00 | |
| 2111601 | PEM | 1,713,927.88 | |
| 2112201 | ACW | 45,719,578.40 |
How and which funtion can be use to bring values from file 2,3,4 to file 1?
NB: In Excel we can do this via Vlookup or Sumif formula. I tried DAX lookup function but not getting results.
Thanks
Hey Eyelyn Qin, Thanks for you efforts, the solution works until unless data type of 'Reference' column from File 1 changed to 'Text'.
I realized that 'SUMMARIZE' funtion creating 'Code' data type as Σ which I am unable to convert to 'Text'
Is there possible solution to if data type is 'Text' for 'Referecnce and Code' column?
Thank you again!
Hi kinmin ,
Actually as long as the types of the columns to be matched in several tables are the same, it does not matter whether it is the Text type or the Number type.
Code in New Table will be the same type as well.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi kinmin
Use SUMMARIZECOLUMNS to add a calculated Table from File
Hi kinmin ,
1. Create a new calculated table:
New Table = var _1=SUMMARIZE('File 2','File 2'[CODE],"Sum",SUM('File 2'[AMOUNT])) var _2=SUMMARIZE('File 3','File 3'[Code],"Sum",SUM('File 3'[Amount])) var _3=SUMMARIZE('File 4','File 4'[Code],"Sum",SUM('File 4'[Total Due])) return UNION(_1,_2,_3)2. Use LOOKUPVALUE() to get the matched sum:
New Vaues required = LOOKUPVALUE('New Table'[Sum],'New Table'[CODE],[Reference])Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hey Eyelyn Qin, Thanks for you efforts, the solution works until unless data type of 'Reference' column from File 1 changed to 'Text'.
I realized that 'SUMMARIZE' funtion creating 'Code' data type as Σ which I am unable to convert to 'Text'
Is there possible solution to if data type is 'Text' for 'Referecnce and Code' column?
Thank you again!
Hi kinmin ,
Actually as long as the types of the columns to be matched in several tables are the same, it does not matter whether it is the Text type or the Number type.
Code in New Table will be the same type as well.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Super Cool! Thanks Millions 🙂
Hi,
You can use Merge tables feature of the Query Editor to mimic the VLOOKUP()/SUMIF() functionality of MS Excel.
