Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm quite new on the use of Power BI and I'm stuck trying to solve this probelm since almost one week.
I have a table containing registers of different flights, it looks as follow:
FlightID | Category | Date | Aircraft Registration | Airport DEP | Airport ARR |
34406 | Production | 06-Jan | EEE | HUEN | HUEN |
34430 | Production | 07-Jan | EEE | HUEN | HUEN |
34433 | Production | 08-Jan | EEE | HUEN | HUEN |
34458 | Production | 09-Jan | EEE | HUEN | HUEN |
34589 | Production | 12-Jan | EEE | HUEN | HUEN |
34383 | Production | 05-Jan | DDD | EBAW | EBAW |
34407 | Production | 06-Jan | DDD | EBAW | EBAW |
34434 | Ferry | 08-Jan | DDD | EBAW | EHTE |
34515 | Ferry | 11-Jan | DDD | EBAW | EGTC |
34579 | Ferry | 11-Jan | DDD | EGTC | EBAW |
34591 | Test flight EQT | 12-Jan | DDD | EBAW | EBAW |
34368 | Ferry | 05-Jan | CCC | EBAW | EHTE |
34232 | Production | 02-Jan | BBB | HUEN | HUEN |
34320 | Production | 03-Jan | BBB | HUEN | HUEN |
34346 | Production | 04-Jan | BBB | HUEN | HUEN |
34231 | Ferry | 01-Jan | AAA | EDDG | EBAW |
34390 | Production | 05-Jan | AAA | EBAW | EDDG |
34394 | Production | 06-Jan | AAA | EDDG | EDDG |
34427 | Production | 06-Jan | AAA | EDDB | EBAW |
What I need is to know where each plane has landed last time, the result should be a table with 3 columns as it is shown on the sample below:
Date | Aircraft Registration | Airport ARR |
06-Jan | AAA | EBAW |
04-Jan | BBB | HUEN |
05-Jan | CCC | EHTE |
12-Jan | DDD | EBAW |
12-Jan | EEE | HUEN |
I have tried to use the function Group By (registration) selection the MAX value of the date, but I cannot add the column airport because I have to include an "expresion", getting the wrong result. I have also tried with the function summarize, but I haven't succed.
Any idea about how to solve this problem?
Thanks a lot!
Marcos
Solved! Go to Solution.
Hi, in Query Editor, you can do
1. Duplicate the Query
2. Group By:
3. Merge Both Tables
4. Expand it select the columns that you desire
5. Ready
Regards
Victor
Lima - Peru
Hi, in Query Editor, you can do
1. Duplicate the Query
2. Group By:
3. Merge Both Tables
4. Expand it select the columns that you desire
5. Ready
Regards
Victor
Lima - Peru
Thank You! Group By is an excellent solution. Much respect to your name Vverlarde. Mucas Gracias.
Thanks Victor, It works now!!!
I was focus on the columns that I wanted to have on my output table, and I never consider to link them by the ID number.
Regards
@marmarfe,
Glad to hear the issue is solved, you can accept appropriate reply as answer to close this thread.
Regards,
Lydia
Add two measures, one for latest date and other for latest arrival airport
Latest Date = CALCULATE(MAX(Table1[Date]), Filter(ALLSELECTED(Table1[Aircraft Registration]), Table1[Aircraft Registration] = MAX(Table1[Aircraft Registration]))) Last Arrival Airport = Calculate(FIRSTNONBLANK(Table1[Airport ARR],1), Filter(Table1, Table1[Latest Date] = Table1[Date]))
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |