This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.
The solution is somewhat easy. I created a simple stored procedure in AdventureWorksDW2019 as below:
CREATE PROCEDURE SP_Sales_by_Date
@date int
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM [dbo].[FactInternetSales]
WHERE OrderDateKey >= @date
END
GO
In Power BI Desktop, get data from SQL Server, then:
Enter Server name
Enter Database name
Select Data Connectivity Mode
Expand the Advanced options
Type in a SQL statement to call the stored procedure like below:
exec SP_Sales_by_Date @date = 20140101
Click OK
Click Transform Data
Now we need to create a Query Parameter. In my sample I create a DateKey in Decimal Number data type:
A while ago I wrote a blogpost on how to use Unicode characters in Power BI. In that blogpost I used a recursive Power Query function to convert Hex values to Dec values. A few weeks back one of my site visitors kindly shared his non-recursive version of Power Query function which beautifully does the job. A big shout out to Rocco Lupoi for sharing his code. So, I decided to share it with everyone so more people can leverage his nice Power Query function. I have touched his code a bit though, but it was more of a cosmetic change, so all credits of this blogpost goes to Rocco. The benefits of his code is not limited to being non-recursive. The code below converts numbers of any base when the base is smaller than 16 like Binary and Oct, so it is not limited to Hex valuesonly. The other benefit of the below code is that it is not case sensitive (note to the digits step on the code below).
Here is the fnHex2Dec function for Power Query:
(input as text, optional base as number) as number =>
let
values = [
0=0,
1=1,
2=2,
3=3,
4=4,
5=5,
6=6,
7=7,
8=8,
9=9,
A=10,
B=11,
C=12,
D=13,
E=14,
F=15
],
digits = Text.ToList(Text.Upper(input)),
dim = List.Count(digits)-1,
exp = if base=null then 16 else base,
Result = List.Sum(
List.Transform(
{0..dim}
, each Record.Field(values, digits{_}) * Number.Power(exp, dim - _)
)
)
in
Result
As you see in the code above, the base parameter is optional, so if not provided base 16 would be the default.
I wrote some other posts on this topic in the past, you can find them here and here. In the first post I explain how to create “Time” dimension with time bands at minutes granularity. Then one of my customers required the “Time” dimension at seconds granularity which encouraged me to write the second blogpost. In the second blogpost though I didn’t do time bands, so here I am, writing the third post which is a variation of the second post supporting time bands of 5 min, 15 min, 30 min, 45 min and 60 min while the grain of the “Time” dimension is down to second. in this quick post I jump directly to the point and show you how to generate the “Time” dimension in three different ways, using T-SQL in SQL Server, using Power Query (M) and DAX. Here it is then:
Time Dimension at Second Grain with Power Query (M) Supporting Time Bands:
Copy/paste the code below in Query Editor’s Advanced Editor to generate Time dimension in Power Query:
let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
#"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ID])), Time.Type),
#"Hour Added" = Table.AddColumn(#"Time Column Added", "Hour", each Time.Hour([Time]), Int64.Type),
#"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each Time.Minute([Time]), Int64.Type),
#"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/5) * 5) + 5, 0)), Time.Type),
#"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/15) * 15) + 15, 0)), Time.Type),
#"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/30) * 30) + 30, 0)), Time.Type),
#"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/45) * 45) + 45, 0)), Time.Type),
#"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/60) * 60) + 60, 0)), Time.Type),
#"Removed Other Columns" = Table.SelectColumns(#"60 Min Band Added",{"Time", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"})
in
#"Removed Other Columns"
Today Microsoft released Power BI Desktop March 2020 which I was hoping that it includes a simple feature on Matrix visual to be able to sort the Martix by column in descending order, but, it doesn’t. So, in this post I quickly show you how to sort Matrix by column in descending order.
Here is the scenario. One of my customers is building a report in Power BI showing sales by Year, Month and Day of Week in a Matrix as below.
Sorting Matrix Visual in Power BI
Everything looks fine! But looking at the Matrix sorting quickly reveals that such feature is NOT available (YET). But the customer would like to see the Matrix sorted by Year in descending order, something like this.
Sorting Matrix by Column Headers in Descending Order
