New Table with data from Other Tables (With different columns)

[RyanRFC]

We have a reservations system where we build packages of cruises/lodges and add on services like hotels, flights, etc. To the system, there are two fundemental types of products:

Type 1: Cruises/Lodges (Table: 'Bookings') -- this table has a lot of columns, because a cruise/lodge needs information like supplier, departure, season, itinerary, promotion, isCharter.. and it pulls all that information from other tables

Type 2: AddOn Services (like flights, land transfers, hotels) (Table: 'AddOns') -- this table is pretty simple because it doesnt pull information from other tables so its just, supplier, sale price, date

I am pretty sure that I need to create a new table that is like a hybrid of these, but I dont know how to do that since they are completely different. Below are a few screenshots if that helps. 

Additional Info:

A CBI (client booking information -- like a travel package) record is the parent of these. The CBI can have multiple bookings and multiple addons. 

Orange is the Booking/Addon Tables that I need to Combine (Green is what booking derives info from)What it looks like in our system

Payments to suppliers are then hooked on to Bookings / addons. Payments to us from Clients are hooked on to the CBI. The difference is our profits. Anyways, let me know if I can clarify any more!

Thanks!

[v-yuta-msft]

@RyanRFC ,

---

Type 1: Cruises/Lodges (Table: 'Bookings') -- this table has a lot of columns, because a cruise/lodge needs information like supplier, departure, season, itinerary, promotion, isCharter.. and it pulls all that information from other tables

Type 2: AddOn Services (like flights, land transfers, hotels) (Table: 'AddOns') -- this table is pretty simple because it doesnt pull information from other tables so its just, supplier, sale price, date

I am pretty sure that I need to create a new table that is like a hybrid of these, but I dont know how to do that since they are completely different. Below are a few screenshots if that helps. 

---

Do you mean to do something to combine "Bookings" and "AddOns" without affecting other tables? Does these tables have relationship like one-one or one-many? From the diagram you provided, it seems like both of them have many to one relationship with "CBIs", you may try many-many model in this senario. About how to create composite model, please refer to doc below:

https://docs.microsoft.com/en-us/power-bi/desktop-composite-models

Community Support Team _ Jimmy Tao

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

[RyanRFC]

They are both one to many. 

A booking can only be part of one CBI, An Add On can only be part of one CBI. But a CBI can have many bookings / addons. For example, a client can have 2 cruises (bookings) and a flight (addon) and hotel (addon). 

The problem is that there is nothing similar about the booking table and the addon table so I am not sure how to go about creating a new table that combines them in a new way. The important data that needs to be in this 'products/services' table (new table that includes both addons and bookings) is the supplier name, the cost, the travel date. 

One thing to note is that there is a relationship between addOn and Booking. This is because theres actually two levels of addons (sorry if this gets confusing now 😄 ) - and the system knows the difference based on if the addon's CBIID column or BookingID column is populated with the corresponding ID or if its null. So, in the case that we buy the product from a supplier like lets say American Airlines, that is considered an CBI level AddOn. In the case that we buy the flights through the cruise provider, this is considered a Booking leve AddOn. The difference is that the first case we need to track a seperate line of payments since its from a new supplier. 

[RyanRFC · Accepted solution]

Ok so here first you have to separate AddOns, using power query. I just used SQL statements to import two seperate tables, Addon-Booking and Addon-CBI. 

Then to my main question, you go to power query and choose Append as New, and then select the two tables, Booking and Addon-CBI. You need to make sure that each table has uniquely named ID columns, so I had changed those from ID, to BookingID and AddonID... Now in the new table I just have those two columns, plus CBI ID (their parent table).

Now they are good to go, and as for any other columns, I can just make them in modelling using the Related DAX statement.