I worked on data cleansing and standardization last week and found out that List.Sum function was extremely useful for grouping the columns altogether. I used to utilise the merged column before, but actually, there is a smarter way of adding the columns. Therefore, I would like to share with you all how to use it.

Let’s get started!

Source:

The datemonth columns perform as a wide format. I would like to sum these columns as “Year”- for analysing purposes.

Step 1: Select Custom Column

Step 2: Type your New Column Name

Step 3: Type the blow formula

= List.Sum ({[Column1],[Column2],[Column3]})

Surrogate keys are integers used to connect the fact to the dimension tables of a data warehouse.

SSIS doesn’t have a built-in solution for generating surrogate keys. Therefore, using a script component to generate a row number for each row of the table is one of the most common methods. This article is to show the steps on how to generate a surrogate key.

In this scenario, we make use of data [Production].[Location] from AdventureWorks2014.

Step 1: Select top 100 of [AdventureWorks2014].[Production].[Location]

  • Get some idea of what data have embedded in this table

Step 2: Create a script

  • Create a…


I worked on SQL training few days ago and learnt how to execute the query with even/odd number criteria.

SCENARIO:

Query a list of BUS STOP NAME from the BUS STOP table that has an even ID number. Print the results in any order, but exclude duplicates from the answer.
The BUSSTOP table is described as follows:


So, if you have data that is stored in a wide format in excel. But, you want to reshape it to a long format. Then, here is the solution for you.

Chloe Wong

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store