How to edit source data after you've created your data model and measures in pwrpivot

[alfred]

Hi Ken

Is there a best practice for power query when you need to edit the source data, particularly after you've loaded it to power pivot and created your reports.

I did some editing in power query and when I tried refresh the data, it gave me an error message (ODE..ODBC something error). Anyways, long story short, I had to amend existing connection to data model to "connections only" and then reload. I was able to load the amended data but I lost all the measures I had previously created which was very frustrating.

Why does this happen?

Does this mean that after you've created your report in power pivot, you will not be able to make amendments to your source data in power query without the risk of recreating the report?

Alfred

 

[Ken Puls]

So short answer to this:

Does this mean that after you've created your report in power pivot, you will not be able to make amendments to your source data in power query without the risk of recreating the report?

Is no, but that comes with a caveat. If you start mucking with column names and measures or relationships refer to those, that could blow things up.

What version of Excel are you using? In Excel 2010 this can be a pretty major issue. I'd load to worksheet first, then link to the data model. In Excel 2013 the fidelity isn't as good as 2016 where PQ is built in to the product.

One thing some users do is store their Power Pivot measures on a disconnected table, just so that they can swap out tables. I've never had the need to do this, but if you are super concerned, you can do that.

 

[alfred]

Thanks Ken

I'm using Excel 2013.

In PQ, I edited an existing custom column which used an IF function, to add an extra category to the logic. Did not add new columns, changed column names or changed data type. The edited source table is actually a merged from two other source tables.

Alfred