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.
Solved! Go to 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,
Happy to help!
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,
Happy to help!
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!