How to return/bring values from multiple data table based on lookup criteria as single column

[kinmin]

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

[kinmin · Accepted solution]

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!

[Anonymous · Accepted solution]

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.

[kinmin]

Super Cool! Thanks Millions 🙂

[Anonymous]

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.

[kinmin · Accepted solution]

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!

[Ashish_Mathur]

Hi,

You can use Merge tables feature of the Query Editor to mimic the VLOOKUP()/SUMIF() functionality of MS Excel.

[aj1973]

Hi @kinmin 

Use SUMMARIZECOLUMNS to add a calculated Table from File

https://www.youtube.com/watch?v=d9l97BfXbOA&t=152s