Forum Discussion
Create table (not a pivot table) from Data Model
I have set up the data in my data model. Used Power Query Editor for ETL. I set up the relationships in the model with a fact table and my 3 dimension tables. Now I want to create a data table in a worksheet that that merges columns from each of the tables. I know I can use the merge feature in power query, but I am working with large data and the load time is ridiculously slow. What we often do if each table does not exceed 1 million rows, is load the tables into a worksheet. Then open MS Access and link to the Excel tables and create queries to merge data and create a table. Then we end up exporting that table back into Excel so we can share that workbook within our organization without the links. But this seem archaic to me. Surely there is a better way?
Most probably you can do that. You may check
Using join functions in DAX - SQLBI
From SQL to DAX: Joining Tables - SQLBI
for the details, plus lot of other materials.
As for the Power BI not sure what do you mean. In general that's the same data model (aka dataset, semantic model) to work with which DAX queries are used; and same Power Query to prepare external data sources (dataflows) or internal one. If you have Power BI Pro or higher licenses you may do all preparations in Power BI Services creating dataflows and/or semantic model data from which will be loaded to Excel. Refresh is scheduled one or initiated by Power Automate. Or manual.
How to return back the table it's better to explain on example. Let say we have some 3 tables
added to data model and having relationships
If you have no one table in the grid, all are structured data sources (aka PQ) created from external sources, add some fake table in the grid like
On the ribbon Data->External connections->Tables->select this or any table, open it
and place the imported table at any desired place. It repeats source place.
From right click menu on that table
Table->Edit DAX and select DAX as command type. In the expression copy/paste (e.g. from DAX Studio) DAX query which returns desired result. For our sample it could be
EVALUATE NATURALLEFTOUTERJOIN( A, B )which gives
So, the main point you need to generate correct DAX query which returns desired result from the data model. It could be based on SUMMARIZE or other integration, simple or quite complex, all depends on concrete data.
SergeiBaklan Thank you so much! I am a novice when it comes to DAX, but I did manage to successfully create a table based on inner join. But, now I need to find how to write a DAX query to pull data from all 4 tables. It has to:
1. Complete inner join with table a and b
2. Add columns from table c based on left outer join.
3. Add columns from table d based on left out join.
Is all of this easier to do in Power BI? I have requested the software from our IT Dept., but have been waiting for weeks.
Most probably you can do that. You may check
Using join functions in DAX - SQLBI
From SQL to DAX: Joining Tables - SQLBI
for the details, plus lot of other materials.
As for the Power BI not sure what do you mean. In general that's the same data model (aka dataset, semantic model) to work with which DAX queries are used; and same Power Query to prepare external data sources (dataflows) or internal one. If you have Power BI Pro or higher licenses you may do all preparations in Power BI Services creating dataflows and/or semantic model data from which will be loaded to Excel. Refresh is scheduled one or initiated by Power Automate. Or manual.
