cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alienvolm
Helper III
Helper III

How to duplicate a table to create static data

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

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

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.

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

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.

View solution in original post

@v-stephen-msft 

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

 

~Alienvolm

mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors