Forum Discussion
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.
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.
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’
’Product’
’Type’
’Gender’
’Records’
Relationship:
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]))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
5 Replies
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.
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.
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’
’Product’
’Type’
’Gender’
’Records’
Relationship:
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]))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
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.
