Adding/Removing Columns in Excel Prior to Refresh

[Anonymous]

I have been trying to figure out the correct steps when editing my source data (NOT data source) - (data source is an excel sheet). Whenever I change a column header on the source table (in excel), or add/remove a column, I get a series of errors that I cannot seem to correct. It seems like something as simple as adding/removing a column (or renaming one) shouldn't be so difficult. Furthermore, the way these errors are explained is pretty lame... if the goal is to make BI more accessable to users then this area needs to be addressed.

Could someone point me in the direction of a video or arcile (and dear god don't point me towards the Micorsoft stuff, because they don't explain it) that isn't a 45 minutes long? I need a quick and simple explanation on how to do this. 

Thanks, 

[MFelix]

Hi @Anonymous ,

Be aware that PBI is self service BI tool however the foundation base is the same as for other BI tools, meaning with this that PBI uses a tabular model of data and any changes on the data source need to be reflected on your PBI file and it's requires changes in the Query Editor.

This is even more clear when you are talking about Excel files or CSV files, this because if you look at the code that is made when importing the Excel file base you see clearly that all the columns with data are specifically refer. For example if you have information on 10 columns of an excel file you will see that the import will only consider those 10 columns adding columns or delete them will cause errors as you describe.

You cannot have a constantly changing setup and your data model to update accordingly to what you add or delete without errors since this is not a fully AI model in terms of the data setup.

When PBI is refered as a Self Service BI is concerning the analitcs behind the data and the easy part to connect to data when talking about more advance changes as any software you need to take the additional steps.

I don't have made any model with dinamic increase / decrease of columns in excel files but theres is a Super User that maybe can give you a hand on this.

@ImkeF ,

Do you know any way to update the query with changes on excel spreadsheet without having to rewrite or have erros on the query?

Also if you see any missinformation on my information above please fell free to clarify.

[Anonymous]

I realize that the query editor needs to be updated... I'm looking for a step by step guide on what exactly that entails. I've tried doing this on my own and I can't figure it out. 

[ImkeF · Accepted solution]

Hi @Anonymous 

usually, when you use the UI to import data, the "Changed Type"-step will automatically be created as well and that leads to problems when the data source changes. So as a first step: Delete that step:

As the second step, select all columns you need, right-click mouse and check "Remove other Columns". This will make sure that any new columns will automatically be deleted and you keep only those columns that have been there when you first created your report.

Then do all further necessary transformation and as the last step of your query, re-apply the type transformation. This can be done automatically: Check all columns -> Transform -> Any Column -> Detect data type. Or manually of course, if you prefer.

If some of the columns that should be kept will be renamed in the future in your data source, then you should consider if that column shouldn't be unpivoted instead. (Say, you import an Excel-sheet with turnover from the last 3 months in columns, starting Jan-Feb-Mar and the next month will be Feb-Mar-Apr, then this should be unpivoted so that you have 2 columns instead: Date and Amount.

If your colum names change because someone has messed up with the data, but it should in general be fixed set of column names, you can demote headers first ("Use Headers as first row") and then rename columns by their position (all new column names will be numbered -> check the columns by their position, and give them new names. Then delete the first row, containing the old column names).

Hope this is short enough? 😉