How to get records from Table B related to an ID in Table A in power bI desktop

[deepali_garg]

Hi All, 

I have 2 sheets in an excel file . Both sheets have a common column ID but have different related Line items.
My requirement is to get all the line items related to a particular ID from Sheet 2 dynamically and show it in a table visual for users to export the data.
For example:
Sheet 1 has 

Sheet 2 

I  have to fetch all the records from Sheet 2 based on ID in a table visual.

[v-yinliw-msft · Accepted solution]

Hi @deepali_garg ,

Yes. You can do it in the Power Query.

You also need to add an index column. Then 

Merge query:

Is this what you expect?

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[deepali_garg]

Thank you for this approach . 
But is there any other way as i want to fetch multiple columns , so creating each calculated column would be a lengthy approach. 
Can I use merge functionality to achieve this?

[v-yinliw-msft]

Hi @deepali_garg ,

You need to add an index column for unique identification.

Table1:

Table2:

In the Power Query:

Add the index column for Table1 and Table2.

Then create the new column in Table2:

New column:

Column = RELATED(Table1[UserName])

Column 2 = RELATED(Table1[Column1])

Column 3 = RELATED(Table1[Column2])

Table2:

Is this what you expect?

Hope this helps you.

Here is the PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.