Match id and create a new table

[NickProp28]

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)

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.

[v-jayw-msft · Accepted solution]

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 

Best Regards,

Jay

[PaulDBrown]

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

[NickProp28]

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.