cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dub Frequent Visitor
Frequent Visitor

How To Get Data From Multiple Tables inside one Column?

Hello everyone,

 

I am trying to solve this for a while now. If anyone can help i will be very much greatful.

 

I need to get data from multiple tables inside another table's column.

 

I tried LOOKUPVALUE() but it can take data only from one table. It somehow needs a loop which will dynamicaly choose a table and column to look at. (The column name is the same for all tables).

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
dub Frequent Visitor
Frequent Visitor

Re: How To Get Data From Multiple Tables inside one Column?

Hi @v-qiuyu-msft@Greg_Deckler

 

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

View solution in original post

6 REPLIES 6
Super User
Super User

Re: How To Get Data From Multiple Tables inside one Column?

Need more details on this: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

CONCATENATE?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

dub Frequent Visitor
Frequent Visitor

Re: How To Get Data From Multiple Tables inside one Column?

Thank you Smoupre,

 

I tried thisColumn = CONCATENATE() which returns a string (in my case a table and column name) - f.ex. 'Table[Number]' - but what i need is a value.

 

SUM('thisColumn') would do the work, but it returns an error "The function SUM cannot work with value of type String".

 

Is there any function or DAX thing to get the expression of a String? Or any other Idea?

 

Thank you for your help.

 

Dusan

Super User
Super User

Re: How To Get Data From Multiple Tables inside one Column?

VALUE will turn a string into a numeric value if possible.

https://msdn.microsoft.com/en-us/library/ee634584.aspx


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

dub Frequent Visitor
Frequent Visitor

Re: How To Get Data From Multiple Tables inside one Column?

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

 

Moderator v-qiuyu-msft
Moderator

Re: How To Get Data From Multiple Tables inside one Column?

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dub Frequent Visitor
Frequent Visitor

Re: How To Get Data From Multiple Tables inside one Column?

Hi @v-qiuyu-msft@Greg_Deckler

 

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

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)