Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
HxH
Advocate II
Advocate II

Copying data into a new table ignoring the data source

Hi everyone
I have a PBIX file in which I uploaded a source for which I am not able to set the gateway connection. Since it doesn't need to be refreshed anyway what I want to do is copy the data into a new table and delete the source. The problem is that I cannot do that by simply copying the entire table and pasting the data into a new one, because it is more than 3000 rows. 

One solution would be to break it up into pieces and then append it back together, however if there was an easier solution I would gladly avoid this work. What I need is to copy the data as is in the final query step into a stand-alone table and delete the old one without breaking the model 

Thanks for any solution 

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

Hi @HxH ,

 

Firstly, We can convert the entire text by Binary.ToText and Json.FromValue function:

 

6.jpg

 

Then we create another Query after we copy the entire output text.

 

7.jpg

 

 

After get the binary file, we can convert it to json then the table.

 

8.jpg9.jpg

 

At the last, we can expand all the column inside.

10.jpg

11.jpg

 

At last, we need to change the type and name of columns and tables name as the same as the origin one to replace it (Please make sure you have a backup before change the query)

 

Because this table is generate from constant text, it does not need to configure gateway in Service.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-lid-msft
Community Support
Community Support

Hi @HxH ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jimmy801
Community Champion
Community Champion

Hello @HxH 

 

but why not copying the data to a retrievable data source (like Excel, txt, csv or whatever) and then connect to that file?

 

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

v-lid-msft
Community Support
Community Support

Hi @HxH ,

 

Firstly, We can convert the entire text by Binary.ToText and Json.FromValue function:

 

6.jpg

 

Then we create another Query after we copy the entire output text.

 

7.jpg

 

 

After get the binary file, we can convert it to json then the table.

 

8.jpg9.jpg

 

At the last, we can expand all the column inside.

10.jpg

11.jpg

 

At last, we need to change the type and name of columns and tables name as the same as the origin one to replace it (Please make sure you have a backup before change the query)

 

Because this table is generate from constant text, it does not need to configure gateway in Service.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
edhans
Super User
Super User

I don't understand what you mean by having a PBIX file with a table as the source. Tables for the most part should not be in PBIX files. I am not even sure if you can have a 3,000 record table in the PBIX file. I've never typed that much and have had paste failures in far smaller tables when playing around.

 

If it is external to the PBIX file, then what exactly are you trying to accomplish?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors