Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
usman1
Regular Visitor

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

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_idscore[0]score[1]score[2]score[3]createdOn[0]createdOn[1]createdOn[2]createdOn[3]
12   01/11/2020   
2467 01/12/202001/13/2020  
36891001/13/202001/14/202001/15/202001/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

2 REPLIES 2
v-kongfanf-msft
Community Support
Community Support

Hi @usman1 ,

 

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

vkongfanfmsft_0-1710495831100.png

vkongfanfmsft_1-1710495844583.png

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.

 

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

usman1_0-1710541216819.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.