Lose measures and reports when I import a data model

This one is a real *****. I receive a finance excel report which has a data model. Thousands of rows of data but I don’t have access to the source.

I spent hours creating a great POWER BI resource but then when I came to import a data model update… ie another version, I lost all my measures and reports.

I had to recreate them by copy and paste. Wasn’t too pleased.
Tabular editor didn’t seem to work.

How can I solve this one?

@Ericadyson,

Ack – that had to have been super frustrating. What about simply converting the Excel updates to CSV before you load them into Power BI in order to “sanitize” them by stripping out the data model information in Excel that would overwrite the work you’ve done in Power BI?

As a “belt and suspenders” safeguard, also make sure that all these options are turned off in your settings:

  • Brian

Hi @Ericadyson, did the response provided by @BrianJ help you solve your query? If not, how far did you get, and what kind of help you need further?

Hi… thanks for reachingout. I did reply, but to the email… which obviously didn’t go anywhere! Here it is…

Hi Brian,

Thanks so much for getting back to me. However, I didn’t understand how your good suggestion could help. The issue is this:

The excel I get has selected pivot tables… I can get access to those, no problem, but I need access to the million + rows of data from the ledgers that sit behind them and the path to the sql export sits on someone else’ computer. So I don’t see how converting the excel I get to a csv file helps. I’m probably misunderstanding something. so perhaps you can help me out here.

Also, another slight complication is that much of the content is in Hebrew and a normal csv doesn’t help.

I guess I need access to the original export, don’t I?

What should I ask for… access to a folder on their drive… then I could access the raw data?

What do you advise? If you can point me in the right direction, I’d be ever so grateful.

Thanks so much. Erica

To receive a resolution in a timely manner @Ericadyson, please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

@Ericadyson,

> What should I ask for… access to a folder on their drive… then I could access the raw data?

That would be my strong inclination, even if it requires rebuilding the data model in Power BI. You obviously will only have to do it once, and I always find that I gain a lot of insight into the structure and content of the data by doing the data modeling myself if I’m going to be the one also building the report(s).

This is also why I always turn off all the import/autodetect relationships options, since I want to be the one making the decisions about my data model.

I hope that’s helpful. Please let me know if I missed the gist of your question.

  • Brian

Hi @Ericadyson, we’ve noticed that no response has been received from you since the 15th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Hi. Thanks for reaching out. No it isn’t solved. I tried Brian’s suggestion but to no avail. I will try to find the time to send you an example if that would be acceptable to you? Many thanks

Hi @Ericadyson, to receive a resolution in a timely manner. Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, a screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

@Ericadyson,

I have no experience with importing Excel-based data models into Power BI, so I don’t know:
a) what caused your measures and reports to get wiped out in the first place
b) whether there’s a more efficient way to bring the data in than my approach below (there probably is…), but the following seemed to work fine for me, in terms of pulling in all four tables from Excel and preserving the Power BI measures and visuals

Here’s what I did:

  1. in the PBIX file, deleted all the tables except your measure table
  2. in PBI Options, made sure to turn off all the auto-detect relationship settings
  3. when into the Excel file and connected DAX Studio from within Excel to the Power Pivot data model
  4. exported all four tables from Excel to CSV using DAX Studio
  5. went back into Power BI, and brought all four tables into PBI through the CSV connector
  6. manually reconnected the relationships among the tables

All data imported, and the measures and visuals remained intact. I’m assuming if you go through this process with your file, afterwards you can just change the data connection setting within PBI and refresh the data directly from the server without having to go through the Excel data model import which caused the problems in the first place.

I hope this is helpful.

  • Brian
1 Like

Ah Brian, you’re a star! Why didn’t I think of using DAX within Excel to extract the tables!!! I had found that PP utilties, but it didn’t dawn on me that that was the route to getting at the tables! I had tried Dax Studio via the pbix and pbit but to no avail.

Actually, there’s quite a bit of cleaning up to do when exported to csv… column types etc and I lost some of the data model measures which I did use… but actually there were only 2 that I needed… total actual and total forecast. All my own measures were based on those and other keys from the fact table and date table that are in Hebrew. But hey, I only have to do that clean up once and next time I am home and dry! Wonderful. Thanks a million Brian 1) for responding 2) for keeping contacting me to see and 3) for solving my problem… which was a big one regarding time and effort.

So… how can I thank you, except to say THANK YOU! Really pleased with Enterprise DNA. I have learned and am learning so much.

@Ericadyson,

Thrilled to hear that worked well for you. Sometimes it just takes someone with fresh eyes looking at the problem in a different way. I had the same experience earlier this week with a problem I totally hit a dead end on. Posted it to the forum, and almost immediately had two terrific solutions employing completely different approaches that had never occurred to me. It really is an amazing community and resource.

Best of luck on your project.

  • Brian