This quick tip explains how to overcome an issue with using CSV or Flat Files and you get additional columns.
I have been working with CSV Files, and what often happens is that you get years added into the columns on a CSV File as shown below is what the original file looks like.
And this is what the source file looks like after we have added some years
Now what happens in the Query Editor is when you import your CSV File it actually hardcodes the number of columns as shown below with our first example which has got 11 columns.
So now what happens is when I update my file to have the additional years and refresh my Preview I see the following.
Now in order to overcome this, whilst it might not be the most elegant solution it works well and I do the following.
I edit the M Code and change it from
To a much higher column
number like 100 for example
What this will then do is to to make the total columns for the table be 100
Next I always either transpose or UnPivot the data.
So once I have done that I then have the following in Query Editor
As you can see it has got the additional columns which do go on for some time.
All that I do is to then filter the Value and remove all Blank Values
NOTE: The reason that I do this, is that as new years with data becomes available it will have a value other than blank (possibly a zero at worst case)
Which then leaves me with my CSV File in the format I require with the additional
years, which will be added dynamically?
As you can see by making a simple change to the code, it means when I import future data via CSV or Flat Files that it will still work showing the updated data.