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 w...
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,

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!