Forum Discussion

osabhaois's avatar
osabhaois
Frequent Visitor
5 years ago
Solved

Dealing with multiple excel file submission and data inconsistency

Hi I am updating a data set on a weekly basis with multiple excel files. Some files are added to a folder and some are connected via onedrive web link.   The files are provided to me by team membe...
  • edhans's avatar
    5 years ago

    If the missing columns are needed for the report, nothing you can do about that as the data is missing and the report won't work.

     

    However, if these are optional columns that you aren't using, then absolutely! Suggestions:

    1. Remove the automatic "Changed Type" step that Power Query puts in. I actually disable that in global options now. What happens is PQ will apply data types to every column, some of which you don't care about and remove. But by explicitly converting the column data type, it will cause an error if that column is missing on the next refresh.
    2. Select only the columns you need, and select Remove Other Columns from the Home Ribbon. Now missing columns will not be a factor as you never actually use them. By using Remove Other, you are actually telling Power Query to "Keep these columns" so you are not naming the unneeded columns in a "Remove this column" step.
    3. Now you can set your data types manually for the columns you need to keep.

    There are other ways to help make your report more robust. DataChant did a series of Pitfalls in Power Query few years ago and it is still 100% relevant. You can read those, or post back here with some more specific issues you are having.