Help Modeling when all fact values are in single column

[heavyarms308]

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. 

FORM	FIELD_NAME	FIELD_VALUE
1	age	16
1	city	dallas
1	state	texas
1	year	2018
1	hair color	brown
2	car	ford
2	model	mustang
2	model year	1999
2	degree	phd
2	job	yes

[v-piga-msft]

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