Trying to make a flat copy, rather than keeping references permanently

[Larry_J_King]

This seems like it should be simple, but even with ChatGPT and Copilot I haven't been able to figure it out.

In PowerBI, I sync'ed from an external data source (a popular online CRM) to get the full contents of TableA.  Based on the time it took and the huge size of the .pbix file, it appears that all the data from TableA has been copied to my local hard disk, which is great.

However, every time I perform an action using PowerQuery, PowerBI re-loads data from the externalsource... which takes a LONG time.

How can I create a local copy that is not dependent on the original?  In Excel it's easy:  select all, and then use Paste Values to replace all dependencies with flat values.  That's what I want to do in PowerBI.

I tried making a second table, TableB, which was a complete copy of TableA.  But it still has the same dependencies!  So then I tried deleting TableA, hoping that TableB would stand on its own.  But TableB's data all become errors because it was just a big reference pointing to TableA.

Thanks for any help you can give.

Larry K.

[Shai_Karmani · Accepted solution]

Power Query queries always re-evaluate against their source, so there isn't a native "paste values" equivalent inside Power BI. Reference and Duplicate both still point back to TableA's source, which is why deleting TableA broke TableB.

To get a truly independent copy you need to materialize the data outside of Power BI and bring it back in as a new source. The cleanest path is to install DAX Studio, connect to your PBIX, and use File > Export Data to write TableA out to a CSV on disk. Then in Power Query create a new query from that CSV and use it in place of the CRM-backed table. You can then delete the original CRM query (or right-click it and uncheck "Include in report refresh") and the new table will stand on its own.

For smaller tables you can skip DAX Studio and just go to the Data view in Desktop, click the table, Ctrl+A then Ctrl+C to copy, paste into Excel, save as CSV, and import that CSV instead.

If this helped, a thumbs up and accepting the solution would be appreciated.

Best,
Shai Karmani

Let's connect in LinkedIn

[m4ni]

PowerQuery will always take from a source and never act as a flat copy.  Consider importing from csv as per feedback from @Shai_Karmani 

[Shai_Karmani · Accepted solution]

Power Query queries always re-evaluate against their source, so there isn't a native "paste values" equivalent inside Power BI. Reference and Duplicate both still point back to TableA's source, which is why deleting TableA broke TableB.

To get a truly independent copy you need to materialize the data outside of Power BI and bring it back in as a new source. The cleanest path is to install DAX Studio, connect to your PBIX, and use File > Export Data to write TableA out to a CSV on disk. Then in Power Query create a new query from that CSV and use it in place of the CRM-backed table. You can then delete the original CRM query (or right-click it and uncheck "Include in report refresh") and the new table will stand on its own.

For smaller tables you can skip DAX Studio and just go to the Data view in Desktop, click the table, Ctrl+A then Ctrl+C to copy, paste into Excel, save as CSV, and import that CSV instead.

If this helped, a thumbs up and accepting the solution would be appreciated.

Best,
Shai Karmani

Let's connect in LinkedIn

[Larry_J_King]

Thanks, Shai!

My data is 411,275 rows.  If I export it to CSV, I need to break it into pieces no larger than 30,000 rows each, correct?  Is there a way around that?

I'll have to check with my IT department to see if they allow DAX Studio as an installation. If they do, that sounds like the best bet.  Thanks!

[danextian · Accepted solution]

DAX Studio allows exporting larger tables but the limit depends on your device's computing power.  There is a portable version which you might need IT for https://daxstudio.org/docs/installation/portable/