Forum Discussion
Adding new query
Hi. I have a school report with several queries coming from a power BI connector and I just created a new one with reading data coming from a local excel spreadsheet. I have pupils names on this excel spreadsheet but I need to allocate the unique ID numbers so that I can have a relationship with other data. Any suggestions of how can I automatically allocate the ID's to the pupils? I have other queries where I have pupil's name and Unique pupils ID.
Thanks
Hii Anonymous,
Technically it should be possible to join in one of the other tables (that have both name and ID, preferably the pupil master table) via the name attribute. Then, you can use the ID and populate a new column.
This approach is problematic though. If there are pupils with the same name, you will end up with duplicates since the join finds two rows for that.
Generally, you should fix that in the source. You have the ID columns as unique keys, so for your data model to work, you should also use them.
I know, this is easier said than done, especially when there are coming spreadsheets from left and right that do not use the IDs.
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
1 Reply
Hii Anonymous,
Technically it should be possible to join in one of the other tables (that have both name and ID, preferably the pupil master table) via the name attribute. Then, you can use the ID and populate a new column.
This approach is problematic though. If there are pupils with the same name, you will end up with duplicates since the join finds two rows for that.
Generally, you should fix that in the source. You have the ID columns as unique keys, so for your data model to work, you should also use them.
I know, this is easier said than done, especially when there are coming spreadsheets from left and right that do not use the IDs.
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/