Forum Discussion
How can I use a data source with variable column names and position?
Our organization would like to visualize the results of a survey questionnaire. This would be easy but the catch is the column names (well, more like question labels) and position may vary over time with new template versions of the questionnaire. For example, our current dataset format would be like the one below:
| Column A | Column B | Column C | Column D | Column E |
| I-1. What is your name? | I-2. Which country are you from? | I-3. What is your birthday? | I-4. What is your blood type? | I-5. What is your gender? |
| John Doe | USA | 1/1/1999 | O | Male |
| Jane Doe | United Kingdom | 1/2/1999 | AB | Female |
| Jim Doe | Canada | 1/3/2000 | A | Male |
| June Doe | Mexico | 1/5/2001 | B | Male |
| Jenny Doe | Brazil | 1/6/2001 | O | Female |
Fast forward to a couple of months, questions may be relabeled, moved around, or completely removed, or new questions may be added. Like in the table below, a new question I-2 (city) was added, the questions for the country, birthdate and gender were moved and relabeled, and the blood type question was removed:
| Column A | Column B | Column C | Column D | Column E |
| I-1. What is your name? | I-2. Which city are you from? | I-3. Which country are you from? | I-4. What is your gender? | I-5. What is your date of birth? |
| John Doe | Los Angeles | USA | Male | 1/1/1999 |
| Jane Doe | London | United Kingdom | Female | 1/2/1999 |
| Jim Doe | Vancouver | Canada | Male | 1/3/2000 |
| June Doe | Mexico City | Mexico | Male | 1/5/2001 |
| Jenny Doe | Brasilia | Brazil | Female | 1/6/2001 |
If I combine the files of the datasets above, it would totally mess up the data because of the varying position and label of the columns (there's basically no formal column name). That said, has anyone come across something like this and what could be the possible solution to make the combined files usable?
5 Replies
Hi.
a possible solution would to open both of the tables as data sources in the Power BI Query Editor. Then, you can choose the option "Append Queries as New". Now, you have several options to JOIN the data. You need to make sure upfront to use the same naming of headders in order to have a good matching.
Cheers
RobHi Rob,
If I'm understanding your suggestion correctly, this means that we should have a different query for each template used? That will only work if we only had 2 or just a couple of templates. However, this case would preferably want to be scalable as we could be working with different survey templates for specific clients, with versions for specific uses or revisions to existing templates.
Also, the header names is unlikely to be the same with each template, again due to revisions/new template versions.
Hi olimilo ,
You can put these data in one excel file and connect to that excel file with Power BI Desktop. When there is any change for original data, please update it in excel file. You can get the latest data after click "Refresh All" link in Power Query Editor. You may get the error "The column xx of table wasn't found" when the column renamed or add new column in original data source. Please refer the content in the following links to resolve it.
POWER BI AND LOCAL EXCEL DATA REFRESH
10 Common Mistakes You Do In #PowerBI #PowerQuery – And How To Avoid Pitfalls
Best Regards
