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 |
