Oops, the data structure changed

Earlier in the week, I did a post on how to use the Covid Tracking Project API and that very night, the structure of the data changed. An extra two columns had been added to the data to better capture the Covid-19 deaths. That slight alteration meant that the refreshing of the data in Power BI did not work.

I can only think of two ways of handling this change: 1) you can re-do the “get data” process or 2) go into the advance editor to alter the program. Option one meant not only re-doing the “get data” but also re-doing all of the steps to transform the data which wouldn’t have been too bad, but I decided to try the second option.

To get to that advanced editor, you first go into “Transform data”:

It is within the Power Query editor that you can find the “Advanced Editor” under the Home tab:

Once you get within the “Advanced Editor”, the programming will look something like the following. Here is where you can alter the program to adjust what you want done. Since I needed to add two additional columns, there were a few rows of codes I needed to adjust because those rows listed out the columns of data fields. These lines of codes with adjustments are highlighted in the image below. (I tried to make the text larger but I was unsuccessful. The main intent of the image was to show how many lines of code I had to alter.)

After making the appropriate changes, try refreshing to make the new changes stick. I had to “Close and Apply” and then refresh once or twice to make Power BI realize that I had requested a new table with new columns to be loaded. Power BI seems to remember the old layout and may need several encouragements before it realizes that something has changed.

To make things easier and to make sure I did not ruin the original program, I first copied the old lines code onto Excel and then added the changes in Excel. Then I copied those lines that I had changed back into the “Advanced Editor”.

That’s one way of making the necessary changes when the original source of data changes. There may be others, I just don’t know them.