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
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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 96 members 1,549 guests
Please welcome our newest community members: