Forum Discussion
Adding/Removing Columns in Excel Prior to Refresh
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? 😉
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.
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? 😉