Copying data into a new table ignoring the data source

[HxH]

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 

[v-lid-msft]

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,

[Jimmy801]

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 · Accepted solution]

Hi @HxH ,

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

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

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

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

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,

[edhans]

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?