How to duplicate a table to create static data

[Alienvolm]

Hi, 

I have a table for IP geolocation purposes: I have downloaded it from an online service into a CSV table, but it's not going to be refreshed, so that's basically static data. 

At the moment Power BI is connected to the CSV file. I would like to create a table that resides in Power BI instead, so I can eliminate the query.

I tried to copy and paste the table into a new tablke in Power BI, but I have more than 1M lines, so Power Bi suggests that I create multiple shorter tables and then merge them with Power Query. 

Is there a better/simpler way? 

Also, would a table that large residing in Power BI impact performance? 

Thanks!

~Alienvolm

[v-stephen-msft · Accepted solution]

Hi @Alienvolm ,

You could learn the merge/append operation from this link: https://radacad.com/append-vs-merge-in-power-bi-and-power-query.

Or you may try to create relationships among multiple shorter tables.

There're three connection type in Power BI: import, DirectQuery and live connection.

Reference: https://radacad.com/directquery-live-connection-or-import-data-tough-decision

The advantages and disadvantages of the three connection type are summarized from the link above.

Import Data

Advantages

• Fastest Possible Connection
• Power BI Fully Functional
• Combining Data from different sources
• Full DAX expressions
• Full Power Query transformations

Disadvantages

• Power BI file size limitation (It is different for Premium)

DirectQuery

Advantages

• Large Scale data sources supported. No size limitation.
• Pre-Built models in some data sources can be used instantly

Disadvantages

• Very Limited Power Query functionality
• DAX very limited
• Cannot combine data from multiple sources
• Slower Connection type: Performance Tuning in the data source is MUST DO

Live Connection

Advantages

• Large Scale data sources supported. No size limitation as far as SSAS Supports.
• Many organizations already have SSAS models built. So they can use it as a Live Connection without the need to replicate that into Power BI.
• Report Level Measures
• MDX or DAX analytical engines in the data source of SSAS can be great asset for modeling compared to DirectQuery

Disadvantages

• No Power Query
• Cannot combine data from multiple sources
• Slower Connection type: Performance Tuning in the data source is MUST DO

When your pbix file is published to Power BI service, gateway is used to refresh data in Power BI service. If your connection is Direct Query, you need to configure the gateway to veiw the report in Power BI service.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alienvolm]

@v-stephen-msft 

Thanks for the comprehensive explanation! This will help a lot! 

~Alienvolm

[mahoneypat]

Yes, you can load it one time and then right click on the query in the query editor and uncheck "include in report refresh".

Regards,

Pat

[Alienvolm]

Thanks! 
I had no idea I could do that... 

At this point, would I still need the source file? I'm asking because I would like to make this report into an app to be shared. Would the data definitely reside in Power BI? 

I also have another question: this report uses direct query mode. Since I added the IP locations table, it has switched to Mixed storage mode, and it requires a gateway. 

With the data residing in Power BI, will I still need a gateway?

Thanks again!

~Alienvolm