Problem changing one data source for another identical one

[Anonymous]

Hello everyone!

I created a data model from one data source. That data source will be updated every week, by adding more data at the bottom. Some of the data we add every week will be repeated, as the data is made up of pipeline snapshots. However, evey week we produce a new Gsheet for this data, and it is saved in a different folder.

I am following these directions that I found to replace one data source for another. I have built the data model on an old snapshot file. What I would like to do is to replace the data source file every week for the one that has the latest data. The structure of the files is exactly the same. The only difference is the new weekly snapshot that is added at the bottom.

If you click on the directions, there are three ways to do this. I couldn't use the first two, as the option to change the source is not highlighted. I think it's because this may not be possible with Gsheets.

The M code option is possible, though. I am able to replace the source data form one table to another. Everything looks good, until I close the transformation window and go to the actual file. All the data starts to reload, adn I get the following error message:

I don't understand the error message. I am not sure if the added rows are creating some kind of conflict. I know for a fact that all the columns are in the same place.

I am hoping that someone can help me to interpret the message, or can help me to find a way to change the source that will work. Otherwise, I will have to build the data model every week, which is not ideal.

Thank you!

[Anonymous]

I tried to eliminate the error by making an exact copy of the original Gsheet, changing the source to that one, and then adding the latest week's data.

The data source change works. After I add the new data and refresh, it's available on the table. However, I build two extra tables with that data. In one of them, I bring the data from the previous week's snapshot, filtering by snapshot date. On the other, I filter for currnet week's snapshot. When I go to these sub-tables, I don't see the latest week's data, which means that I can't update the model.

All measures are built in a third sub-table, which is a join of the previous week and current week's sub-tables.

I don't understand why the OG source table's data is not being picked up by the sub-tables. Could someone please explain?

Thank you!

[AW11]

I believe you are not having an issue with pulling the data, but with the relationships within the model. It is saying your identifier field has duplicate values which cannot happen. In Power Query, try removing duplicates in column 'ID2' and then refreshing.

[Anonymous]

Hi! Thank you for explaining the error message.

I suppose the duplicate would be in the new data source, since I was not having this problem with the old one. I will have to go through it, since there really should be no duplicates. ID2 is a concatenation of opp ID and date of snapshot.

I will take a look and circle back. Thank you!