Forum Discussion
How To Get Data From Multiple Tables inside one Column?
Thank you for the answers!
The problem is that i have much more tables involved. But i managed to get what i need by appending all the one row tables into one big table, and then use LOOKUPVALUE to pull the desired column values based on matching Ids.
Thank you both for your contribution.
BR
Dusan
MergedData = CONCATENATE(CONCATENATE("'";CONCATENATE([TableName];"'"));"[Number]")
This is my formula.
'TableName' is a column from current table containg names of my desired tables. Let's say table names are Table1, Table2,...Table10.
Basically, I need a number from the columns Table1[Number] and Table2[Number] ... and Table10[Number] - (these tables have only 1 row)
My formula returns a string 'Table1'[Number], 'Table2'[Number] ... 'Table10'[Number]'
But the formula
VALUE(CONCATENATE(CONCATENATE("'";CONCATENATE([Table1];"'"));"[Number]"))
Returns an error "Cannot convert value '''Table1'[Number] of type Text to type Number."
Can you understand? Or maybe i should create a pbi example file?
Thank you very much for your time.
Dusan
Hi dub,
From your description, each table has [Number] column which has one value, you want to concatenate those column values in one table, right?
In your scenario, assume Table2, Table3 and Table4 have Number column, you can try to create a calculated column in the table Table2 use DAX below:
ConcatenateCol = CONCATENATE(CONCATENATE('Table2'[Number],CONCATENATE(",",MAX('Table3'[Number]))),CONCATENATE(",",MAX('Table4'[Number])))
Please take a look at attached pbix file.
Best Regards,
Qiuyun Yu
Thank you for the answers!
The problem is that i have much more tables involved. But i managed to get what i need by appending all the one row tables into one big table, and then use LOOKUPVALUE to pull the desired column values based on matching Ids.
Thank you both for your contribution.
BR
Dusan