Data manipulation

[Anonymous]

Hi I am fairly new to Power BI so im having an issue with manipulating the data to conform to my requirements.

I'm looking into average prices for are fare per complete travel and the issue I'm having is that some of the travels are spit into two entries corrupting my average price. I'm also setting up zones i.e. national travel within UK, international travel within EU etc.

The data is structured as follows

Name             Date        Booking Ref      Depature    Arrival    Routing        Price

John Doe       01.01.19     1XE5G              LHR            AMS       LHR/AMS     1000

John Doe       01.01.19     1XE5G              AMS           LAX        AMS/LAX      2000

Note there might be travels that contain 3 flights between First departure and final destination

I need the data to look like this

John Doe       01.01.19     1XE5G              LHR             LAX        LHR/LAX      3000

In order to get closer I have made one collumn contain name, date and booking ref. as a unique ID and then all the arrival/departure as the next collums as shown below

ID                                              Trv 1    Trv2     Trv3    Trv4

Johndue01.01.191XE5G            LHR     AMS     AMS    LAX

Now I would like to remove any entry that isn't unique so that I just end up with original Departure and Final Destination.

I get that I might have done this in a non optimal way so any input would be highly appricated 

[Thim]

Here is how i would fix it.

I have skipped modelling so the Departure and Arraival matches on all lines, with the same booking ref.

Instead i have made a new Routing, that shows the true departure, and true arrival for a flight on all lines.

You will need to make 3 new columns.

First columns.

True departure = IF(

LOOKUPVALUE('Table'[Arrival ];'Table'[Arrival ];'Table'[Depature ];'Table'[Booking ref];'Table'[Booking ref])=BLANK();

"Departure";

"Not Departure"

)

the first column outputs Departure if this line has the first departure of the travel, and Not Departure, if this is not the first departure

I replicate this with arrival, so second column should look like this.

True Arrival = IF(

LOOKUPVALUE('Table'[Depature ];'Table'[Depature ];'Table'[Arrival ];'Table'[Booking ref];'Table'[Booking ref])=BLANK();

"Arrival";

"Not Arrival"

)

Now i make the third column called True Routing.

True Routing = CONCATENATE(

LOOKUPVALUE('Table'[Depature ];'Table'[True departure];"Departure";'Table'[Booking ref];'Table'[Booking ref]);

CONCATENATE("/";

LOOKUPVALUE('Table'[Arrival ];'Table'[True Arrival];"Arrival";'Table'[Booking ref];'Table'[Booking ref])

))

This will output LHR/LAX for John Doe in the data example.

If it is important for you to have the Departure and Arrival on seperate columns as well, then take the 2 LOOKUPVALUE formuals in the third coulumn and add them in their own columns.

Do note, this will only work, if all travel for the booking ref is in the same day, otherwice it will still seperate the value, unless you have worked this in your meassures, or simply don't add the date column in your matrix.

Hope this help. 🙂

[Anonymous]

Worked really well 🙂 Thanks alot for the help 

[v-eachen-msft]

Hi @Anonymous ,

If your problem has been resolved, you could accept the reply you like to close the case.

[Anonymous]

Thanks for the input will try it out as soon as I can 😄