cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NickProp28
Post Partisan
Post Partisan

Match id and create a new table

Dear community,

 

Please have a look at this pbix: https://ufile.io/7kcqtsrm

At the main table, the ConsolID is unique. While in SubTable, the ConsolID is duplicate since there have many shipment ID attached to a single ConsolID.

 

Request: I want to create a new table of Maintable using ConsolID (Unique) and join to Subtable whenever the ConsolID is match to Maintable.

Expected new table. (For C00680735)

NickProp28_0-1612259707151.png

Since I have million of line in DB, can you guys suggest a way which can create a table and run in efficiently. 

 

Appreciated any help provided.

 

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @NickProp28 ,

 

You could use Merge Queries as New feature in Query Editor to create a merged table then remove the unnecessary columns.

https://docs.microsoft.com/en-us/power-query/merge-queries-overview 

2.PNG

3.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

Hi @NickProp28 ,

 

You could use Merge Queries as New feature in Query Editor to create a merged table then remove the unnecessary columns.

https://docs.microsoft.com/en-us/power-query/merge-queries-overview 

2.PNG

3.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

PaulDBrown
Super User
Super User

@NickProp28 

You can join both tables in a one-to-many realtionship by the ConsolID and then build the visuals using the field from main table ConsolID and add the rows you need from both tables.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Dear @PaulDBrown ,

Thanks for your attention. 

I tried in this way, linked one-to-many relationship, create a matrix then put the main table ConsolID. Drop the necessary field from both table. But its took a longer time to process since I have huge table.

So I'm thiking isit possible to create a a new table which will reduce the loading time of the report.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!