Dec 01 2020 07:43 AM
Hello Together,
this is my first activity here. I hope, I am doing everything right and describing everything corretly and understandable.
At first I describe my source data, then what I am doing with it and at the end the challenge, that I am unable to achieve without you.
My source is a CSV-file with financial bookings. Here an example:
I am importing and processing it with the Power Query Editor.
As nearly all sourrounding information are in the field „verwendungszweck“ (subject), I am extracting some other information out of it.
I am able to give every booking a category. I need that for later analysis.
I am also able to read out the flight number if there is one.
So out of „Mietzahlung für Flugzeug - Boeing 727-100C - Reg.- D-AHLS - GMI8398“ I am getting the additional colum „flightnumber“ with the value „GMI8393“, and also the additional colum „registrierung“ with the value „D-AHLS“.
Here an example:
The flight number is always different and I don’t know it before.
My challenge:
I would like to add the registration tot he rows where I know the flight number.
So going from:
Getting to this:
My Problem:
I cannot „hardcode“ it, because I don’t know the flight number before.
So I would need some logic like:
If you find a match between a flight number and a registration, then fill the same registration into the empty registration-fields oft he same flightnumber.
Is that possible to automate? How can I do that?
Nothing is secret on this project. It is for learing purposes for me so the data comes from a video-game and can be shared without any doubt.
Best regards & thanks in advance for the help.
Lasse
Dec 01 2020 08:07 AM
@Aeraxon Certainly possible. With your file to play with, I suggest you try this:
In PQ, sort your date first by Flight number, then by Registration number. Now I can't remember if the Registration numbers will show at the top or bottom of the each group of flight numbers . If at the top, use Fill down or if at the bottom use Fill upp.