Hello Folks!!
I was just working with Power BI Desktop another day and was wondering how to create a table on which I can create schema of the table as well as enter the data at my will, and eventually found a very interesting way of creating table at Power BI desktop report level which allows developer to enter the data manually, and it also allows developer to even edit the earlier entered data with equal ease.
Though, this is one of the functionality already available for Power BI Desktop developer however it is bit tricky to find where to do the needful changes which will deliver desired output.
So in nutshell, I would call it as "Creation of manual dataset or Table for Power BI Desktop Report".
No doubt, it is quite basic to deal with, however very important feature of Power BI Desktop to explore on.
Let's start the road roller with some illustration.
Requirement :
Suppose, requirement is to create some Power BI report for sales data of say ABC organization, there they need to have numerous kind of bar chart, line graph , Tablix etc. however they are maintaining only country ID in their data and they do not have any source data for country with respect to id which they can provide as a data set, so customer asked you to create one manual table and insert mapping of country with id (Client will help while proving information) but at the same time they do not want to place this country data at any public or private location such as FTP, SFTP location etc. so basically they want country data will be abstracted to the world and will not be available at any public and private platform so that world will not interact with the same.
Resolution :
For a moment, let's assume there is no restriction in placing file at any public and private platform and client is fine if their data is available to the world.
In such a scenario, life will be easy and this feet can be achieved either by placing a file (csv, excel, flat file, xml etc.) that having corresponding country data at say Share Point or One Drive location OR create a table in the database with required schema and manually insert the data into the same, and then onward, it will be referred by Power BI and eventually used in the Power BI Report.
Yeah, above will work if we do not have to maintain the data abstraction, it can achieve the purpose of both Data Integrity and data Security but it needs attention and requires additional resources and human efforts to maintain the other platform where we can place these files, it could be Share Point or One Drive but both or any needs maintenance of whatever the platform was chosen for placing file.
So obviously, above objective can be achieved via aforementioned way but with few exceptions to deal with which can be proved dud for the client.
I have explored more on Power BI and found a well-defined way of achieving our objective which is demonstrated below in detail.
Power BI Dataset or Table creation with manual data Entry
Let's say we have report names SALES where we need to introduce new table that we are going to create manually, we will do the activity via several steps, please go through with one by one.
Step 1 : Open Power BI Desktop
Step 2 : Open Power BI Desktop Report named SALES
Step 3 : Navigate ribbon available at the top and Click on Home (by default Home will get open, if in case it is not, please follow what mentioned in this step)
Step 4 : Navigate to Enter Data and Click on Enter Data as shown in the image below
Step 5 : Once Enter Data was clicked, pop up will get open and guide you to create table manually.
Step 6 : Based on the requirement, please create table columns named as Country Name and Country ID and provide name to the table at the bottom of the page, let's say Country.
Step 7 : Navigate to the Left Top corner of the page, you will find three option to close the page, out of many select Close and Apply and click on the same, as soon as Close and Apply was clicked, new table or dataset with name Country will be created and very much available to that respective SALES report.
Power BI Manual Dataset OR Table Data Editing
Power BI also provided the way to EDIT the data which we have entered manually previously, and that's obvious to have this functionality as this is quite essential and in demand as well i.e. developer needs to update the provided data or even insert the new records as days in future.
Nonetheless, at one hand, it is quite easy and straight forward task to create table manually and then insert data into it however editing of already entered data is not that straight forward, work around is very much there but it is quite hidden contrast to Data entry option.
After exploring a bit, I eventually found the way of how to achieve the goal and same has been detailed below.
Step 1 : Open Power BI Desktop
Step 2 : Open Power BI Desktop Report named SALES
Step 3 : Navigate Right side of the page and you find dataset/table named Country.
Step 4 : Right Click Country and click on EDIT
Step 5 : New page will get open, on the right, you will find Query Setting tab.
Step 6 : Under Query Setting, there is Applied Steps
Step 7 : Under Applied Steps, click gear against Source as shown in the image below
Step 8 : Now, Same Create Table screen will get opened (where data was entered manually earlier), and now this will allow to EDIT the data as well.
So while following above steps one can easily create manual tables, can enter data manually and most importantly, can edit the entered data as well.
As rule of thumb everything in this world having pros and cons, this is also not exception, please check below for the same.
Pros :
1. Extra resource or efforts not required to maintain a standalone.
2. No such limitation in number of such table creation.
3. Fast in data processing.
Cons :
1. Aforementioned created table "Country" will only be used inside the report SALES, i.e. it scope of usage is inside the specific report only in which it was created, it is not possible to share this dataset / table with other report even in the same workspace, its scope is limited to its report only.
2. No way to update or insert record automatically, need to do it manually.
No comments:
Post a Comment