Empower Your Organization with Data Conversion Lead for Organizations

How to create outlook signature

here's a really cool feature if you're using excel at work did you know that you can have all of your work-related data so information on employees on products or on customers inside a single excel cell and this way you have all the latest information across all of your excel files and it's not just you but your colleagues have the latest information too you can use these to create templates that you can send out to others basically you're gonna have one source of truth how cool is that let's set one up together first of all let's clarify one thing not everyone is going to be able to create data types that are available to the entire organization and that's a good thing only a few people are gonna have the access to do that and you might not be one of them so after you watch this and you think this is useful forward this video to the person who can implement this for you now just to give you a quick overview of what rights are needed for this take a look at this now feel free to pause this video and go over this in more detail but we're going to do some role playing and this way you can picture what's possible based on these different points of views okay so assume it's my first day at work as an hr intern and i received this list from my boss what he wants is for me to create a report where he can search for or select an employee's name see their image the department they work in and how many years they've been in the company now the only information i have is this but there's something strange about these names they have a little suitcase beside them when i click on the suitcase i get this whole card pop up i have additional information about this employee here i can see their age the number of years they've been with the company their salary and so on and when i click on any of these on this icon right here i get that content inserted on the cell beside it and if i click on this icon on top i get all the fields in list view so if we go with gender that populates to the side now when i click on this cell i notice that it's referencing the original cell and then it has dot gender for this one and for the other one here it has the first name so all of these formulas are dynamic what the suitcases are organizational data types because he's already added these organizational data types i don't need to have access to the original data set everything is cached all i need to have is excel for office 365 which i have for my job so now i can go ahead and quickly create what i was requested to do so i'm going to add a data validation list for source we are going to select all of these names now this is not an official excel table so i'm just going to add some additional blank rows for future employees now check this out because i have office 365 and i have this new feature searchable drop-down list or autocomplete drop-down list i can start typing the name of the employee and we see it populated here so let's just press tab and select that now right here we wanted to get the image so i'm going to use what we saw before start off with the equal sign select my data type and then type in a dot and select what we need from here so we wanted the image of the person let's select that press enter and we get the image automatically populated in here we wanted department and we wanted the number of years the person is with the company that was under tenure let's select that and press enter that's just the number of years so let's make this more readable type an and space years and that's it we're done with the request now if i change my selection to ben or if i start typing e kelly that's what i want we have the start of a very promising dashboard now as the senior hr specialist who is responsible for designing these templates i need to have a power bi pro license to be able to apply these data types so i have the names of the employees i'm just going to select them go to data and then select that data type you'll find your recently used up here these are the ones from the organization if you don't see what you need you can click on more from your organization in this case these are employees i'm going to click on them and that's going to transform these into the employee data type now if it can't find something or let's say you just type only a part of the name so i know that her first name is jessica but i'm not sure what her last name is it's going to add a question mark here and it's going to give you some available options and then you can click on select and it picks up the correct name now you can have different data types here so let's say you have a list of your products i'm just going to try with book and see what we get there is a product data type in the organization so let's select that there are no actual books there are ebook accessories so let's go and click on that and now we have a product data type with different types of options like which department the product belongs to which category it's in the cost and so on of course don't forget to refresh this so that you can grab the latest data from power bi in excel once i've set this up and converted my text to data types i can forward this to someone who doesn't have a power bi pro license and they can continue to use this now what if i want a data type that doesn't exist here so i want someone from our power bi department to add a new data type i get it approved by the my managers and now it's their turn to add this in for me now as the power bi responsible in your company you get the request by the hr department to add a new data type for consultants the request is approved so you open up rbi for desktop and you start by grabbing the data the data could be anywhere it could be an sql on the web or data you have so many different options in this case it happens to be in an excel workbook if you have your data in excel it does make sense to have these on a sharepoint drive and not have it somewhere on your own c drive so in this case i just have it somewhere on my drive so i'm going to just open it up the file only has a single sheet that's the data let's go ahead and transform this we get the ability to add any columns that we need and also make sure that the data types are correct so in this case we don't really need to do anything special with this but it would help to add a new column for age so i have date of birth here let's quickly add that inside power query so we're just going to go and add a column date age we get the age in days hours minutes and seconds so let's go to duration and change this to total years we don't need all that detail so let's go to transform rounding and round down okay so we don't need the previous age here let's delete that and change the header to age let's update the query name and call this consultant now let's go and load this under home close and apply the data is now loaded into the data model we're going to go to the data tab and adjust the formatting of any of the columns that we need so remember data types and formatting are different things to get the correct format show up in excel we need to update the formatting here so for example for salary i want to see that as a currency so let's just quickly update that here and for bonus level let's get that as a percentage now for images so i don't want these to be text i want these to actually show up as images so i'm going to update the data category instead of uncategorized we want to switch this to image url this is going to show us the image and not this path that we see here now it's important to note that these are all valid links so all of these images are loaded on a website and can be accessed by anyone now at this point you can go ahead and add more measures you can add more tables and relate these together but we're keeping this simple we just want to load the information we have here as a data type in excel so let's select this under properties scroll all the way down and you're going to see is feature table we're going to turn this on enter a description for row level i'm going to select name now this is the text that you're going to type in to convert to a data type in excel so be mindful of what you select here for the key column i'm going to go with id now click on save and now we're ready to publish this to the power bi server so click on publish if you haven't saved your power bi desktop file now is the time to save it let's click on save select the workspace that you want to publish this to i'm going to publish it to company reports and click on select and then we just have to wait a little bit for this to get published to power bi okay so it's finished let's open it up directly in power bi go to workspaces company reports and we can see it right here to grant people access to this data set select a data set go to more options manage permissions here you can add different users from your organization who you're going to allow to access this data set once you're done you can make this data type easy to discover by going to more options again but this time to settings if you scroll down under endorsement and discovery you can promote this data type this way the data set is going to be made discoverable and that others in your organization are going to be able to find it by name tables columns and so on we're going to click on apply and then we just need to wait a bit for this to sync and show up on the excel side one thing to mention is that if you're connecting to files that are not on the cloud like i did in this case so if your data is on premises for example you need to activate the gateway connection to be able to refresh the file if you connect it to sharepoint you're going to see data source credentials active so that you can click on it and manage your credentials directly from here okay so now i've opened up excel let's go to the data tab and see if we can find it here we have consultant right here so let's test it out i'll type in the name alisa because i think that's the name of one of the consultants let's go and convert this to the consultant data type there's only one elisa alyssa michelle that's her picture and we get more information about her now the hr specialist can go ahead and create any necessary templates that they want to distribute to others in the department so what do you think can you see this being useful at your organization what data types would you create and how would you use them share that with us in the comments i hope you enjoyed this video i hope you found it useful give it a like subscribe if you aren't subscribed yet and i'm gonna see you in the next video [Music] you