Best way to model this data?

[Anonymous]

I have data similar to the following on the left. Where the values are listed under the date column for which they are incurred. It is extremely disorganized and hard to create DAX formulas with the data like that. 

My question is would making the data on the left look like the data on the right be the best way to model such data? If not what would be an alternative? If that is the best way, what would be the best way to make sure the transformed data still has a primary key? Create another column with concatenated values to create a unique id?

Would this have any implications on load times?

Finally, if this is the best way to transform the data, would it simply be to unpivot all the date columns?

Thank you in advance for any help!

[v-xicai · Accepted solution]

Hi @Anonymous ,

You are right, the best way is to use Unpivot feature, select on the remain columns except these Date  columns, right click to choose option "Unpivot other columns", then rename the result column "Attribute" with "date", don't forget click button "Close & Apply".

In addition,  Unpivot feature won't have influence with the Primary Key.

Generally, the Primary key is created/defined at data source level and then import it into Power BI.  Also you may use DAX in Power BI Desktop to combine several columns so that each row has unique values, like DAX below.

Primary Key= [Column1]&" "&[Column2]



Primary Key= CONCATENATE([Column1], [Column2])

Best Regards,

Amy 

Community Support Team _ Amy

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

[Ashish_Mathur]

Hi,

In the Query Editor, select the first 3 columns > right click > Unpivot other columns.

Hope this helps.