New column in table that is result of SQL Server function ran with parameter from same table.

[Anonymous]

So I have a Table/Query set up, and I need to add some information to that table, with a new column.

I can't calculate the result with DAX, so I created a function in my SQL Server.

Visualized with simple tables:

Table Bookings: (Role Booker is the one who booked the room, role Guest is someone joining him)

 [Booking_ID]   [Person_ID]    [Name]    [Hotel_ID]    [(New Column)County]        [Role] 

       1234                1               Andrew          1                         County1                 Guest

       1234                2               Mark              1                         County1                 Booker

Now, County is stored on Person, but only on the one who has the role of "Booker" fills out their county.

So person with ID = 1 does not have a county. But I can use "Booking_ID" to get the county.

So, is there a way for me to say that the value of X column, is the result of a function with a single parameter from the samle table?

Sorry if this is unclear, let me know if you have any questions.

The thing is, there is a bizzarre relationship between the tables.

[ibarrau · Accepted solution]

Hi. Ok, let's assume you have merge the table with person_id in order to get the country like this:

countryFillProblem

You have your data for the bookers only but you want it too for the guests on the same booking id.

Then reorder the data ascending by Booking_Id and Role:

= Table.Sort(#"LastStep",{{"Booking_ID", Order.Ascending},{"Role", Order.Ascending}})

After this you will be able to Fill Down the country column with data.

 FillDownCountry

There you have your result.

Regards,

[Anonymous]

Hello!

Thank you for the reponse!
I have already found another solution, quite similar to yours. 
I created a merged table of Person and Booking, and then used the data exporer to filter out all null values until I was left with only two columns (Booking_ID and Country), then I merged this "lookup table" with the table I actually needed it in.

It might be a worse solution than yours, so I'll look into implementing your solution.

Thanks!