Forum Discussion

olimilo's avatar
olimilo
Post Prodigy
5 years ago

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 ...
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

olimilo's avatar
olimilo
Post Prodigy
5 years ago

Hi Rena,

What we ended up doing was turning the data sheets into data tables in Excel and loading them as such in PBI. Afterwards, we unpivoted the data, merged it with a master question-ID-field name table to get merge the questions into common data points (where applicable) and finally pivoted it back.

 

It's not the most graceful solution but if anyone has a better solution than this, we're all ears.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi olimilo ,

    Could you please provide some screenshot about original data sheets and your expected result(include master question-id and questions)? Thank you.

    Best Regards