Forum Discussion
Getting first and second row from related table
I have a related table (1..*) like this:
| Id | Name |
| 1 | Foo |
| 2 | Bar |
| 3 | Blah |
In my main table, i want to select the first and second "Name" columns, e.g, this would be my main table:
| SomeField | SomeOtherField | Related1Name | Related2Name |
| abc | def | Foo | Bar |
Thanks!
6 Replies
Anonymous , what is a relationship? do you need it one one side or many side ?
as of now it seems like
first name = maxx(filter(Table1, Table[ID]=1), Table[NAme])
Second name = maxx(filter(Table1, Table[ID]=2), Table[NAme])
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8Thanks for helping with this. As i said below though in response to amitchandak , the "Id" column is identity, not the row number. I want the "first" and "second" rows, not the rows with "Id=1" and "Id=2"
In other words, the table could look like this:
Id FK Name
1 100 Foo
2 100 Bar
3 101 Blah
4 101 Paa
So, for FK 100, i want id's 1 & 2, but for FK 101, i want id's 3&4. So the "first" and "second" row, for each "FK".Hi Anonymous ,
As you explained, you can try to add an index column in Power Query. Then you can get the 'first/second' row.
If you want to add an index column with the grouping, refer to
Create Row Number for Each Group in Power BI using Power Query
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Try to use ALL function
Related1Name = CALCULATE(MAX('Table'[Name]),FILTER(ALL('Table'),[Id]=1))Related2Name = CALCULATE(MAX('Table'[Name]),FILTER(ALL('Table'),[Id]=2))Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
