extracting data from diferents tables

[usuario112]

Hi, im trying to extract the first number of each subject of each student and create a new column to save them, one problem is the tables are not connected each other so i cant use RELATED (they're like 5 tables away from each other), idk how can i do that in power bi and i cant filter it in an excel bcs it is in a database.

 

[v-tianyich-msft · Accepted solution]

Hi @usuario112 ,

It seems to be taking the row data for the minimum value of each, and you can check the results as follows:

First = var _t = ADDCOLUMNS('Table',"First",MINX(FILTER(ALL('Table'),[Premer_nombre]=EARLIER([Premer_nombre])),[Calificacion]))
RETURN MINX(_t,[First])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[usuario112]

It worked!, thank you so much @v-tianyich-msft and also thank you @Greg_Deckler for the help, i appreciate you both 😄

[Greg_Deckler]

@usuario112 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[usuario112]

@Greg_Deckler basically i want this table but the problem is the qualifications has all test notes in one column, they're not separated so i need to filter them by choosing the first user, his subject and his test note, one user can have many subjects and many test notes and i want the first one of all of them. What i dont know is how to make the filter in the table qualifications so when i put those columns (the name of the user, his subjects and test notes) in the table to visualize it i can see only the first test note for every subject and user.

i cant just in power query make a filter because the qualification table is this and i cant know what test note belongs to who

 

[Greg_Deckler]

@usuario112 Still not quite following but you will likely need a date or index on your table with the subject and test notes so that you can define what is "first". You would do that by sorting in Power Query and then adding an index column. Once you have that, it's basically a Lookup Min/Max problem: Lookup Min/Max - Microsoft Fabric Community