cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zamurr
Microsoft
Microsoft

Combine multiple tables into one table

 

I have a massive data model that is getting big and slow. I was hoping for a way to combine a few of the tables into one big table. I know in Excel you can do it with a series of VLOOKUP, but I am hoping to accompish this in Power BI.

 

powerbidatamodel.png

 

The data model is setup with linking tables, 1 to many relationships.

 

I am not sure if DAX can do this or not. I have read a bunch of articles but nothing seems to explain it quite like i want. Merge and Append do not have the logic behind them that i need.

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi zamurr,

 

>> I am not sure if DAX can do this or not. I have read a bunch of articles but nothing seems to explain it quite like i want.

Obviously, dax support this feature.

 

You could use selectcolumns/addcolumns , related, lookupvalue functions to achieve your requirement.

Below is my sample:

Tables: ‘Person’, ’Product’, ’Type’, ’Records’, ’Gender’.

‘Person’

 Capture.PNG

’Product’

 Capture2.PNG

’Type’

 Capture3.PNG

’Gender’

 Capture4.PNG

’Records’

 Capture5.PNG

Relationship:

 Capture7.PNG

Dax formula:

Detail = SELECTCOLUMNS(Records,"Person Name",RELATED(Person[Name]),"Gender",LOOKUPVALUE(gender[Describe],gender[Index],RELATED(Person[gender])),"Type",RELATED('Type'[Type Name]),"Product Name",RELATED('Product'[Product Name]),"Price",RELATED('Product'[Price]),"Amount",Records[Amount],"Total",[Amount]*RELATED('Product'[Price]))

 

 Capture6.PNG

 

Notice: if the columns have the relationship you could simple use Related() function to get the specify values. Otherwise, you could use LookupValue() to search the specify column.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

Hi zamurr,

 

>> I am not sure if DAX can do this or not. I have read a bunch of articles but nothing seems to explain it quite like i want.

Obviously, dax support this feature.

 

You could use selectcolumns/addcolumns , related, lookupvalue functions to achieve your requirement.

Below is my sample:

Tables: ‘Person’, ’Product’, ’Type’, ’Records’, ’Gender’.

‘Person’

 Capture.PNG

’Product’

 Capture2.PNG

’Type’

 Capture3.PNG

’Gender’

 Capture4.PNG

’Records’

 Capture5.PNG

Relationship:

 Capture7.PNG

Dax formula:

Detail = SELECTCOLUMNS(Records,"Person Name",RELATED(Person[Name]),"Gender",LOOKUPVALUE(gender[Describe],gender[Index],RELATED(Person[gender])),"Type",RELATED('Type'[Type Name]),"Product Name",RELATED('Product'[Product Name]),"Price",RELATED('Product'[Price]),"Amount",Records[Amount],"Total",[Amount]*RELATED('Product'[Price]))

 

 Capture6.PNG

 

Notice: if the columns have the relationship you could simple use Related() function to get the specify values. Otherwise, you could use LookupValue() to search the specify column.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

this looks like it is exactly what i am trying to do, however when i try to recreate your example i get this error

 

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

This is going to sound like a total noob question, and it should as i am a noob. Once you create your data model, how do i input the DAX formula you sent me.

 

Will it go in as a measured column? a new column?

 

I have been googling but haven't found much

Found it. Modeling -> New Table.

a_mixed_life
Resolver I
Resolver I

Would it be easier for you to create a SQL table (Data Warehouse) that combines them all. Create SQL View against that table, then connect your PowerBI to the SQL View and Dimension tables? That should speed it up.

Kris

That would defintely be faster, however, Most of those tables are from Excel files and are static. But I think creating a SQL view would be ideal. thanks.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors