how to model new 'array' score columns for a single product

[usman1]

hi Guys

I am struggling to model data which is being imported quarterly, from Flat file CSV data set.

The columns are in 'array' type format, and new columns are expected on quarterly refresh takes place

so score[0] is corresponding to createdon[0] column and score[1] is corresponding to createdon[1] column, if data exists...

Product_id	score[0]	score[1]	score[2]	score[3]	createdOn[0]	createdOn[1]	createdOn[2]	createdOn[3]
1	2				01/11/2020
2	4	6	7		01/12/2020	01/13/2020
3	6	8	9	10	01/13/2020	01/14/2020	01/15/2020	01/16/2020

  how can i model, to display e.g. average product Score, against a Date Dimension?

(remember, that new 'array' style columns will be added, in every new feed)

thank you

[v-kongfanf-msft]

Hi @usman1 ,

First, You should Transform the data to unpivot the score and createdOn columns in power query.

Then create a date dimension table and use the following formula to ask for the average value.

Average Score =
AVERAGEX (
    FILTER ( ALL ( 'Table' ), 'Table'[Date] = MAX ( 'Table'[Date] ) ),
    'Table'[Value]
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[usman1]

Thank you Adam

The createdOn[0] and Score[0] should be on same line

and The createdOn[1] and Score[1] should be on same line

Rest of the line need to be deleted

Is it possible, to only display relevant rows?

thank you again in advance