Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nsaraf
Frequent Visitor

Joining derived tables

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.

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

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
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors