Joining derived tables

[nsaraf]

Hey everybody,

I understand how merging of two tables can be done in PowerBI.

But I have two derived/calculated tables -- and I do not see them in the Power Query Editor. So I cannot merge them it appears.

The Derived tables have 1:1 relationship except that the second one has less number of rows but an extra column.

Table1: col1, col2, col3  (1 mil rows)

Table 2: Col1, col2, col3, dollars  (100K rows)

Both are 1:1 on col1 (which is a composite of Col2 and Col3).

How do I acquire an outer join where all 1 mil rows from Table 1 are retained and only 'dollar' values for matching rows from Table2 are extracted?

How can I even Export the calculated table?

Thanks in advance.

[v-cgao-msft]

Hi @nsaraf ,

//How do I acquire an outer join where all 1 mil rows from Table 1 are retained and only 'dollar' values for matching rows from Table2 are extracted?

Please try this calculated table.

Table = ADDCOLUMNS('Table1',"Dollar",LOOKUPVALUE('Table2'[Dollar],'Table2'[Column1],'Table1'[Column1]))

Since the number of rows in the table is particularly high, this consumes a lot of memory resources.

//How can I even Export the calculated table?

Power BI supports exporting data from visuals, but the number of rows to be exported is limited, so you need to filter the table and export it multiple times before merging the data.

Please refer to this document.

Export data from a Power BI visualization

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[lbendlin]

If they are derived tables then you must have access to the actual source table(s) in Power Query. Use that instead.

Why do you need to merge the tables ?  Let the data model do the work for you.

You can execute DAX queries against your dataset and extract the contents of your "tables" that way (there are some limitations so you may need to use chunking).