cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heavyarms308 Visitor
Visitor

Help Modeling when all fact values are in single column

I'm trying to model data that comes from electronic forms, Think of Tax forms where the forms are different, the fields in the forms are different and change from year to year.  In other words, I cannot count on the forms being consistent or the fields contained in the forms to be consistent. All of this has been crammed into three columns of data.

 

The relational data model contains a single fact table that contains basically three columns.   

FORM - tells me the form that I'm looking at

FIELD_NAME - tells be the fields on the form

FIELD_VALUE -  tells me what has been entered into the form 

 

I thought about pivoting the whole thing, but the number of columns could potentially be hundreds or even thousands. 

If anyone has some suggestions on how to model this in PBI or what is the best practice please let me know..

Example of the data. 

 

FORMFIELD_NAMEFIELD_VALUE
1age16
1citydallas
1statetexas
1year2018
1hair colorbrown
2carford
2modelmustang
2model year1999
2degreephd
2jobyes
1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: Help Modeling when all fact values are in single column

Hi @heavyarms308,

 

I have a little confused about your sceanrio.

 

If I understand your scenario correctly that the data you post is your sample data so what is your desired output based on the sample data?

 

If it is convenient, please share your expected output so that we could help further on it.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.