Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to 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.
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.
NewStep= Table.Pivot(PreviousStepName,List.Distinct(PreviousStepName[Demographic]),"Demographic","Demographic Value",each _{0}?)
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.
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:
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
User | Count |
---|---|
24 | |
13 | |
11 | |
10 | |
7 |
User | Count |
---|---|
43 | |
26 | |
21 | |
16 | |
12 |