Create table (not a pivot table) from Data Model

[Txtcher]

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?

[#2]

@Txtcher 

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.

[#3]

@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.

[#4 · Accepted solution]

@Txtcher 

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.

[#5]

@Txtcher 

One more point. If your source is SQL Server and all tables you use are in the same database, with some conditions query folding could work. That means all operations will be done on the server and you need to load only resulting table.

Alternatively you may use native database query Import data from a database using native database query - Power Query | Microsoft Learn with SQL statement within the query. With that also all transformation will be done on the server.

[#6]

Thank you. Unfortunately, the data we work with comes from multiple sources, ODBC, SalesForce, and Access databases on a server. We have some Access databases that import data from all three of these sources at once.