Author: Vitalie Ciobanu

How to turn off automatic upgrade of Power BI licenses

~ Vitalie Ciobanu

Go to https://app.powerbi.com/. Using the gear icon go to Admin Portal and make sure Tenant settings is selected.

Here, there are 2 options you can use, depending on your organization’s needs.

  1. To prevent auto-assignment from happening again you need to disable the “Allow users to try Power BI paid features” option.

Of course, if there is a need, this can be enabled only for specific security groups or enabled for entire organization except specific security groups.

What this will do is, once the end user tries to access a Pro Workspace (for example), they will not be able to access the Workspace and will see below message instead.

As you can see, the user still has the option to go online and buy a license if @he wants to, although this may not be what most organizations want. Here is where the next (2nd) option below comes handy.

To go even further and prevent end users from buying Power BI licenses on their own, you can direct them to a specific internal “software request” page where they can actually request this license or follow your internal Software Asset Management process.

Also from Admin portal – Tenant settings, under “Publish “Get Help” information” – “Licensing requests”, you can paste the URL to your internal software request page or any other page where you have some information on how someone can request Power BI licenses.

What this will do is, once the end user tries to access a Pro Workspace, they will not be able to access it and will see the message below instead.

Clicking on Upgrade account, that Bing page (in my example) will open. Imagine that this can be an internal page for requesting software, licenses or just FAQ documentation page, as per your organization’s needs.

Oracle SQL: Update table with data from another table

~ Vitalie Ciobanu

Task:

Let’s suppose you have Table1 with a million rows and you just altered the table by adding a new column. This new column is empty (NULL) in Table1 but you want to update it with the values that are in a column in Table2.

Solution:

First, do not ruin your day, create a backup table with the same structure and data as TABLE1 by running below query:

CREATE TABLE TABLE1_BKP AS SELECT * FROM TABLE1;

Then test the update query by running below query:

UPDATE TABLE1_BKP A SET
A.COLUMN_TO_UPDATE = (SELECT B.COLUMN_WITH_DATA FROM TABLE2 B WHERE B.COLUMN_WITH_DATA IS NOT NULL AND B.ID = A.ID)
WHERE A.ID IN (SELECT B.ID FROM TABLE2 B WHERE B.ID = A.ID);

If the update went well, run the same query again but replace TABLE1_BKP with TABLE1.

It goes without saying that any UPDATE and DELETE queries must have a WHERE clause to limit the result as much as possible, in case something unexpected happens. And that’s why you need a backup table and test on it first.

Windows PowerShell vs PowerShell

~ Vitalie Ciobanu

Yes, at least for now, there are 2 different PowerShell apps out there and both can run on the same computer. I envision Microsoft will slowly remove Windows PowerShell from shipping with Windows by default in the future. It may be replaced by PowerShell or by nothing, leaving it to user’s discretion whether she/he wants to install it manually or not.

There are some differences, modules, cmdlets changes between the two apps. There is a pretty good/complete article that details all the changes, differences etc. Check it out here:

Differences between Windows PowerShell 5.1 and PowerShell 7.x

“Must declare the scalar variable” error in Toad for SQL

~ Vitalie Ciobanu

Started learning how to work with variables in T-SQL. Doing first basic exercise to declare today’s date and then print it, failed mizerably.

I was using Toad for SQL because this is my day to day tool. Tried several other examples, searched online for this error… looks like “it works”, but it doesn’t for me?!

Opened Microsoft SQL Management Studio, pasted same example, hit F5. Boom, worked! Ok, then it’s something related to Toad for SQL.

Searched again with Toad word included. Of course, found the issue description quickly on Toad’s forum: https://forums.toadworld.com/t/data-point-error-must-declare-the-scalar-variable/27407/8

This is not a bug and that is why things are not changing. This is original behavior from Toad for Oracle.

There are different execution needs depending on what you are doing.

When you have a script you might want to only execute the SQL you are working on. For this you use F9 and we figure out what the current statement is based on your cursor and only execute that.

Or when you have a script you might want to execute all in the script from top down. For this you use F5.

There is a third option which is to execute from the current statement to the end. For this there is a menu action but no hot key.

In the case of a SQL that contains a script variable you can’t execute the current statement as the variable must be declared in block of code that is being sent to the server. So in this case F5 is the only option.

Power Query – Week start and end dates in same column

~ Vitalie Ciobanu

Someone asked me the other day below thing: “On the same column I want a Range of dates not Duration between two dates So I have a Creation date and fulfillment date I need to come up with a column of weekly Range like for example
10/21 – 10/27
10/28 – 11/01
11/02 – 11/09
Something like this.”

The solution can be pretty simple and mostly done through Power Query’s GUI.
Add a date range first using either way you want.
Then under Add Column – Date, you can easy add “Start of Week” and “End of Week”.

Note that if your week starts on Sunday, you don’t need to provide the first day of the week. I need my week to start on Monday so I added 1, instead of 0 or nothing “Date.EndOfWeek([Date],1)“.

The only thing to do now is to merge these two columns and put a separator of your choice. This will give you a column with Week’s start and end dates in the long format.

ex1

If you want month and date (or only date) you need to extract month and then date separately from both your Start and End of Week columns. Then merge month and date from Start of the week and again from End of the week.

At this moment, you only need to merge these 2 last columns, remove all unneeded columns and that’s it.

short

Here’s the entire query I ended up with.


let
Query1 = let
Source = #date(2019,1,1),
Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}})
in
#"Converted to Table",
#"Changed Type" = Table.TransformColumnTypes(Query1,{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Date", Order.Descending}}),
#"Inserted Start of Week" = Table.AddColumn(#"Sorted Rows", "Start of Week", each Date.StartOfWeek([Date],1), type date),
#"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],1), type date),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted End of Week", "Long range", each Text.Combine({Text.From([Start of Week], "en-US"), Text.From([End of Week], "en-US")}, " - "), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Merged Column", "Month", each Date.Month([Start of Week]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Start of Week]), Int64.Type),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Day", {{"Month", type text}, {"Day", type text}}, "en-US"),{"Month", "Day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Start"),
#"Inserted Month1" = Table.AddColumn(#"Merged Columns", "Month", each Date.Month([End of Week]), Int64.Type),
#"Inserted Day1" = Table.AddColumn(#"Inserted Month1", "Day", each Date.Day([End of Week]), Int64.Type),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Day1", {{"Month", type text}, {"Day", type text}}, "en-US"),{"Month", "Day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"End"),
#"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Start", "End"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Short range"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns2",{"Start of Week", "End of Week"})
in
#"Removed Columns"

Examples of Count, Count IF, Count duplicates in SQL

~ Vitalie Ciobanu

Count:

SELECT [ProductModel], COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel]
ORDER BY CNT DESC

Count “if”:

SELECT [ProductModel], [Name],COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
WHERE [ProductModel] LIKE '%Frame' AND [Name] LIKE '%52' --here's the IF statement
GROUP BY [ProductModel], [Name]
ORDER BY CNT DESC

Count duplicates:

SELECT [ProductModel], COUNT(1) CNT -- select a field you want to search for duplicates in
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel] HAVING COUNT(1) > 1 -- if count is greater than 1, then it is a duplicate by that field
ORDER BY CNT DESC

Group count fields a tabular form:

SELECT [ProductModel], [Name], COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel], [Name]
ORDER BY CNT DESC

ITIL 4 coming soon

~ Vitalie Ciobanu

More info on https://www.sysaid.com/blog/entry/whats-coming-in-itil-4.

Quoting blog post’s summary:

  • ITIL 4 is coming soon – early 2019.
  • It is an evolution of ITIL, not a revolutionary replacement.
  • It builds on many of the ideas in ITIL Practitioner.
  • It focusses more on how things fit together to create value, and less on process steps.
  • It’s going to be released in stages, with Foundation material available early in 2019.

Power BI Data Profiling – distinct vs unique

~ Vitalie Ciobanu ~ 1 Comment

“Another month, another Power BI Desktop update” as Guy(s) in a Cube use to say.
Power BI Desktop October 2018 update brings in quite a few updates, with one thing being my favorite as I always do/check it manually when working with new data: Data Profiling. For more info and how to enable it, visit Microsoft’s official Power BI blog post or the detailed post from Matt Allington on https://exceleratorbi.com.au.

Ok, I don’t want to make it a long post, so here’s the thing: one of the Data Profiling feature is “Column distribution”.

Column Distribution allows you to get a sense for the overall distribution of values within a column in your data previews, including the count of distinct values (total number of different values found in a given column) and unique values (total number of values that only appear once in a given column).

So, distinct and/or unique. For anyone else these 2 words may seem similar, but for Data Guys like us, these are 2 different things (just like empty and null 🙂 ). Still confused? Here’s a very simple example to clear things up:

Let’s say we have a small company with 10 employees using 10 laptops from different Manufacturers: HP, Dell, Apple and Lenovo.
Manufacturers in Power BI's Column distribution
Distinct: We have laptops from 4 different Manufacturers (HP, Dell, Apple and Lenovo) aka “total number of different values”, regardless of how many of each we have.
Unique: We have only 2 laptops that nobody else have in our company (one from Apple and another one from Lenovo) aka “total number of values that only appear once”.

Thinking legacy, here’s the same thing in Excel, using countif or countifs.
Excel CountIF formula