Convert three columns with repeating rows into a primary key, field columns, and associated rows?

[Theycallmesnack]

Hi PowerBI folks, 

I'm at the last stretch of a fairly lengthy process where I ingested and parsed a series of XML files into constiuent tables. Each table contains a primary key (survey ID), a field column, and a value column. 

The issue is that each of the fields is on its own row with its associated value. In order to get this to work, I needed to change the field name to columns, but allow the value column to stay as rows (it's difficult to explain, but hopefully the example helps). Thus having the survey ID be unique, the fields being individual columns, and the values populating for each field under the associated column. 

Ultimately, I was able to format the tables containing numeric values by pivoting the field column. Unfortunately, I'm running into issues with the demographics table where both the field and value columns are strings. I've tried simply transposing the data (first trying it with one of the columns, then two, then all three) but I keep getting a complexity error. 

I've also tried grouping the data into lists, then using =table.columns() with a delimiter, but that doesn't preserve the value column. 

Here's an example of the format I need the tables in (the successful one with numerical values)

Here's an example of the one I'm struggling with (It's an Excel table because I needed to anonymize everything, the demographic field is meaningless, pretend they're all different values):

SURVEY_ID    Demographic    Demographic Value

2996734628	ADJSAMP	Spoon
2996734628	AGE	Llama
2996734628	AGERG	35 - 49 Yrs
2996734628	CONTFEED	Yes
2996734628	DISTRIB	Internet
2996734628	DVC_TP_E	Mobile Phone
2996734628	DVC_TP_S	Mobile Phone
2996734628	ITADD2	Pistacio
2996734628	ITAGE	47
2996734628	ITCITY	New york
2996734628	ITCLAIMC	7825
2996734628	ITCLAIMD	20220125
2996734628	ITCLAIMR	XXXXXXXXXX
2996734628	ITCLAIMT	M
2996734628	ITCLINIC	Department of Truth
2996734628	ITCONTRA	H0174
2996734628	ITDIAG_2	Chronic Silliness
2996734628	ITDISDAT	1/18/2022
2996734628	ITDOB	6/7/1974
2996734628	ITETHNIC	-2 N/A
2996734628	ITE_FLAG	N
2996734628	ITLOCATI	DOT
2996734628	ITLOCCD	LC19
2996734628	ITLOC_AD	XYZ
2996734628	ITLOC_CI	NY
2996734628	ITLOC_ST	NY
2996734628	ITLOC_ZI	782082127
2996734628	ITMDSPEC	FAMILY PRACTICE
2996734628	ITMD_F	Bob
2996734628	ITMD_L	Ross
2996734628	ITMD_NAM	Bob Ross
2996734628	ITMD_TYP	Physician
2996734628	ITMEM_ID	28888744
2996734628	ITMOBILE	2106092957
2996734628	ITNPI	1417453903
2996734628	ITPHONE	2016092957
2996734628	ITPLAN	kazoo
2996734628	ITPLANDE	Kazoo
2996734628	ITPLANNA	Medicare
2996734628	ITPRIDIA	I10
2996734628	ITPROC_C	99441
2996734628	ITPROVFG	Primary Care
2996734628	ITRACE	-2 N/A
2996734628	ITSERVTY	MT0102E
2996734628	ITSEX	New york
2996734628	ITSITE_I	7825
2996734628	ITSPECCD	20220125
2996734628	ITSPECIA	XXXXXXXXXX
2996734628	ITSTATE	M
2996734628	ITSTATEC	Department of Truth
2996734628	ITSVCDAT	H0174
2996734628	ITTAX_ID	Chronic Silliness
2996734628	ITZIP	1/18/2022
2996734628	LANGUAGE	6/7/1974
2996734628	PAGELINK	-2 N/A
2996734628	REVISION	N
2996734628	SEX	DOT
2996734628	SITEID	LC19
2996734628	TIMECOMP	XYZ
2996734628	VST_TYPE	NY
2996737032	ADJSAMP	NY
2996737032	AGE	782082127
2996737032	AGERG	FAMILY PRACTICE
2996737032	CONTFEED	Bob
2996737032	DISTRIB	Ross
2996737032	DVC_TP_E	Bob Ross
2996737032	DVC_TP_S	Physician
2996737032	ITAGE	28888744
2996737032	ITCITY	2106092957
2996737032	ITCLAIMC	1417453903
2996737032	ITCLAIMD	2016092957
2996737032	ITCLAIMR	New york
2996737032	ITCLAIMT	40432425
2996737032	ITCLINIC	60644725
2996737032	ITCONTRA	XXXXXXXXXX
2996737032	ITDIAG_2	M
2996737032	ITDISDAT	Department of Truth
2996737032	ITDOB	H0175
2996737032	ITETHNIC	Chronic Silliness
2996737032	ITE_FLAG	10/25/1926
2996737032	ITLOCATI	6/7/1974
2996737032	ITLOCCD	-2 N/A
2996737032	ITLOC_AD	N
2996737032	ITLOC_CI	DOT
2996737032	ITLOC_ST	LC20
2996737032	ITLOC_ZI	XYZ
2996737032	ITMDSPEC	NY
2996737032	ITMD_F	NY
2996737032	ITMD_L	782082128
2996737032	ITMD_NAM	FAMILY PRACTICE
2996737032	ITMD_TYP	Bob
2996737032	ITMEM_ID	Ross
2996737032	ITMOBILE	Bob Ross
2996737032	ITNPI	Physician
2996737032	ITPHONE	2710375537
2996737032	ITPLAN	3237672895
2996737032	ITPLANDE	3764970254
2996737032	ITPLANNA	4292267612
2996737032	ITPRIDIA	New york
2996737032	ITPROC_C	80857025
2996737032	ITPROVFG	101069325
2996737032	ITRACE	XXXXXXXXXX
2996737032	ITSERVTY	M
2996737032	ITSEX	Department of Truth
2996737032	ITSITE_I	H0176
2996737032	ITSPECCD	Chronic Silliness
2996737032	ITSPECIA	1/18/2022
2996737032	ITSTATE	6/7/1974
2996737032	ITSTATEC	-2 N/A
2996737032	ITSVCDAT	N
2996737032	ITTAX_ID	DOT
2996737032	ITZIP	LC21
2996737032	LANGUAGE	XYZ
2996737032	PAGELINK	NY
2996737032	PROVID	NY
2996737032	REVISION	782082129
2996737032	SEX	FAMILY PRACTICE
2996737032	SITEID	Bob
2996737032	SP	Ross
2996737032	TIMECOMP	Bob Ross
2996737032	VST_TYPE	Physician

Any assistance would be greatly appreciated! 

Thanks, 

-Snacks

[HotChilli]

Apologies for the late reply.  I try not to worry too much about performance until it becomes an issue.  If you follow good practice for designing the model (bearing in mind the user requirements) i.e. get the granularity of the fact tables right, create a star schema with dimension tables to slice and dice the data, you can't go far wrong.

I think the main issue you face at this stage is this part "want to keep their options open".  The ideal of presenting a dataset which is a utopia for users is a bit of a pipedream. So try to get a few examples of what they want to do (even if they want to replicate existing reports).

Good luck.

[wdx223_Daniel]

NewStep= Table.Pivot(PreviousStepName,List.Distinct(PreviousStepName[Demographic]),"Demographic","Demographic Value",each _{0}?)

[HotChilli · Accepted solution]

Pivot the demographic column (demographic values in the Values section) and "Don't aggregate" under Advanced.

I think that's what your after.

Let me know.

[Theycallmesnack]

Thanks HotChilli! (and Daniel as well. Ultimately the command is doing the same thing),

That's exactly what I needed. Can't show you the entirety of the end result, but here's a snippet: 

That leaves me with a quick f/u question that's much lower stakes :-): 

Now, I've got a few options for the last step: I'd like to be able to alias the fields using the lookup table included in the XML (but loaded separately...the folks who built these XML files had some interesting ideas...). So here's what I've got planned: 

1. Merge the lookup table with the Question/Answer table, then delete the original question field leaving only the aliased headings (once I pivot out that field). 
2. Leave the three final tables as is (Question/Answer, Demographics, Comments), relate them by the newly unique survey ID, and then pull them into Visualizations/tables as needed Or...
3. Merge all three tables leaving one particularly wide table with all the questions, answers, and comments in a single table. 

Using method 2 or 3 will yield the same final result, but thinking of the end users, which of these two is likely to yield better performance? I'm unfortunately in a situation where the end users "want to keep their options open" for final visualizations, so I don't have any idea what sort of dashboards they're going to have us cook up, and which fields are or aren't particularly important. 

Thanks again, this was incredibly key! 

-Snacks