Wednesday, October 12, 2016

Deleting all duplicate rows but keeping one

How can I simply remove duplicate rows?

WITH cte AS (
  SELECT[foo], [bar], 
     row_number() OVER(PARTITION BY foo, bar ORDER BY baz) AS [rn]
  FROM TABLE
)
DELETE cte WHERE [rn] > 1

Wednesday, September 28, 2016

How to connect to an Excel file on onedrive with Power BI desktop

In the Power BI web version is it possible to open a file on a OneDrive location. In the desktop version this is not possible in the standard way.

On a non-standard way it is possible to connect Power BI desktop to a file on a One drive location.

Here are the steps to follow:

Step 1 - Go to your OneDrive online environment and login


Step 2 - Open the desired file in Excel


Step 3 - Go to File --> Info


Step 4 - Select the link above the Protect Workbook button. Select Copy link to clipboard.


Step 5 - Use this link in Power BI desktop


Step 6 - Paste link


Step 7 - Remove the ?web=1 part at the end of the link, click OK. Login with your OneDrive/o365 account.

Tuesday, September 27, 2016

How to sort month names in Power BI

When names of months used in Power BI you'll be able to sort this A-Z or Z-A. Both do not give the correct order:

A-Z


or Z-A


This problem can be solved in some simple steps. 

Step 1 - Add a month number column to your dataset, like this:


Step 2 - Click month column in data tab of Power BI, like this:


Step 3 - Go to the modeling tab and click Sort By Column, like this:


Step 4 - Click MonthNr in the drop down


Now your report looks like this






Tuesday, February 16, 2016

Running Totals / Cumulative sum in Reporting Services

How to get a running total in SSRS?

Use this Expression

=RunningValue(Fields!Gemiddeld.Value, Sum, nothing)



Result (normal SUM vs Runing Totals)


Friday, February 12, 2016

Cumulative sum in SQL

This is one of the possibilities to calculate cumulative sum in SQL.
select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum
from @t t1
inner join @t t2 on t1.id >= t2.id
group by t1.id, t1.SomeNumt
order by t1.id
Result
 ID | SOMENUMT | SUM |
-----------------------
|  1 |       10 |  10 |
|  2 |       12 |  22 |
|  3 |        3 |  25 |
|  4 |       15 |  40 |
|  5 |       23 |  63 |
When grouping is required, it is better to use this solution (only SQL2012 and later)
select 
 cte.EPICSystem_Title
 ,cte.Uren
 ,cte.Realisation
 ,cte.Datum
 ,cte.Budget
    ,sum(cte.uren) over (partition by cte.EPICSystem_Title order by datum) as CumUren
 ,sum(cte.Realisation) over (partition by cte.EPICSystem_Title order by datum) as CumRealisation
from cte

Wednesday, February 10, 2016

Add date stamp to SSIS export files

When creating a file in SSIS it can be useful to incorporate a date and/or a time stamp into the name of the file, like this:



1) Select the connection manager:


2) Go to properties, Expressions, click on  the dotted button



3) In the popped up screen, choose ConnectionString:


4) Put the following expression in the Expression Builder:


"\\\\sftp.xxx.nl\\xxx\\csv\\IncomingGoods_" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) +
RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + 
RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"

Every backslash in the path needs to be typed twice so \\sftp.xxx.nl\xxx\csv\ becomes  \\\\sftp.xxx.nl\\xx\\csv\\ 

5) Save and run

6) When you also want the time use this:

"\\\\sftp.xxx.nl\\xxx\\csv\\IncomingGoods_" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) +
RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + "_" +
RIGHT("0" + (DT_WSTR,2)DATEPART("hh",GetDate()),2)+
RIGHT("0" + (DT_WSTR,2)DATEPART("mi",GetDate()),2) + ".csv"

Interoperability and Coexistence (Integration Services)

The following table lists some of the differences between the current and earlier versions of Integration Services.