Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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.

 

Need Help
  • ibarrau's avatar
    ibarrau
    6 years ago

    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,

2 Replies

  • ibarrau's avatar
    ibarrau
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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!