Power BI Desktop – Working in Query Editor (part 2/3)

We will be discussing on how to work in query editor. It would be good to refer my previous article Power BI Desktop – Connecting to Data (part 1/3) to get a better understanding of the example we are using here.

So, time to look at the data in the query editor!

Fix the Headers

We are in the stage of data preparation right here and we see that the column names are not good. We see that a better column name would be the second row instead.

To do this you need to first delete the first row. In the Home tab click Remove Rows, Remove Top Rows. Specify 1 (row 1) and click Ok.

Now, click Use First Rows as Headers to set the columns. With that we are finished with the work on our rows!

Fix the Columns

You can select a column by clicking on the header and then navigate with the LEFT and RIGHT arrow keys on the keyboard.

We can keep all the columns but we don’t think we would need “Country/Series-specific Notes” column. So, we can go ahead and delete that. For that you can simply select the column and click Remove Columns on the Home tab. Similarly, you can delete some last columns such as “Column 14“.

Now you will notice that the are some year columns, we think they are not displayed in the best way!

Wouldn’t it be better if we changed simply into two columns, one column displaying all the years in all the corresponding rows of that column and the second column showing the GDP data!

To do that, select the first-year column, press and hold the SHIFT button on the keyboard and select the last column. This will select all the year columns. Now, on the Transform tab click Unpivot Columns.

Now you will notice that we simply turned, multiple columns into one single column where all the years are displayed in the rows of that column.

You can now rename the column names, by double-clicking the columns.

Now we do not want to load any unwanted rows in our data model, for example we can filter out all the non-applicable GDP data from the set.

Now we have a clean data, but the values in GDP column are formatted as text. It would be good to change the format to Decimal number. Lets go ahead and do that as well.

You can also, select multiple columns and click Detect Data Type in the Transform tab.

Now we have finished our work in the query editor, in this data preparation step. The next step now is to load this clean data into the actual data model and create visualizations.

In the following topics, check out what we can do with this data:

Power BI Desktop – Working in Data Model and Creating Visualization (part 3/3)

Creating Super User Using SQL Query

Creating Super User in NAV 2016 using SQL Query

Get the SID of the current user login

Open the command prompt.

Type the following command and click enter to get the SID of current user login:

whoami /user

Get SID of all users in the domain

Type the following command and click enter to get the SID of all the domain users:

wmic useraccount get name,sid

Run the query in SQL

Open Microsoft SQL Server Management Studio.

Right-click on the database in which you would like to create the user and select New Query.

Copy the following query and paste it in the New Query window:

NOTE: Make sure to replace <Domain Name>, <User Name> and <SID> with correct information.

DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50)

SET @USERNAME =
‘<Domain Name>\<User Name>’
/* Domain Name\User Name */

SET @USERSID =
NEWID();

SET @WINDOWSSID =
‘<SID>’
/* USER SID*/

INSERT
INTO [dbo].[User]


([User Security ID],[User Name],[Full Name],[State],[Expiry Date],

[Windows Security ID],[Change Password],[License Type],[Authentication Email],[Contact Email])


VALUES


(@USERSID,@USERNAME,,0,‘1753-01-01 00:00:00.000’,@WINDOWSSID,0,0,,)

INSERT
INTO [dbo].[User Property]


([User Security ID],[Password],[Name Identifier],[Authentication Key],[WebServices Key],[WebServices Key Expiry Date],[Authentication Object ID])


VALUES


(@USERSID,,,,,‘1753-01-01 00:00:00.000’,)

INSERT
INTO [dbo].[Access Control]


([User Security ID],[Role ID],[Company Name],[Scope],[App ID])


VALUES


(@USERSID,‘SUPER’,,0,‘00000000-0000-0000-0000-000000000000’)

GO

Click Execute, to run the query.

After successful execution, the specified user is created in the database. Please check Users page in NAV.