Create a new table using an existing table with distinct values and substrings

[fleetingImage]

Hi,

I have a fact table that looks like this:

fact_sales:

Comparative	Region	Country	Product	Month	Sales
2020 Plan	APAC	Thailand	t-shirts	1	$           10.00
2020 Plan	APAC	Thailand	shorts	1	$           20.00
2020 Plan	APAC	Thailand	snickers	1	$           34.00
2020 Plan	APAC	Thailand	balls	1	$           20.00

I'm importing this table from an Excel file.  Using this data I want to create a new table to use as a dimension (from column "Comparative").  So the new dim table will look like this:

dim_comparative:

Comparative	compYear	compActual
2020 Plan	2020	1
2020 Forecast	2020	1
2020 Actual	2020	1
2019 $c	2019	0
2018 $c	2018	0

The "Comparative" column holds the distinct values from the fact table.  the "compYear" columns holds the first 4 characters from Comparative, and "compActual" shows a 1 when compYear is the current year, and 0 otherwise.

I've been investigating different alternatives like GENERATE(), DISTINCT() and such, but I wasn't able to figure out how to do this.  Also, the new table should be derived from the already loaded table or I should import the data from Excel twice, one for the fact and one for transforming the data into the dim table?

Thanks for your help! 

[amitchandak · Accepted solution]

@fleetingImage ,Try like

selectcolumns(distinct(table[Comparative]), "Comparative",[Comparative],"compYear", left([Comparative],4), "compActual", if(left([Comparative],4) =year(today())))

or

distinct(selectcolumns(Table, "Comparative",[Comparative],"compYear", left([Comparative],4), "compActual", if(left([Comparative],4) =year(today()))))