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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DemoFour
Responsive Resident
Responsive Resident

Primary Key's and ID's - Dataverse created to Query Editor Index

Hi folks, 

I just wanted some advice, I have a data source from Dataverse for my model, with the fact and dimensions coming from the tables created. Within in the tables is the system generated ID and Key - these are Text and Numbers. 

From reading best practice Keys and ID's should be intergers, so is it worth using the Table.AddIndexColumn and removing the columns from the model, or just keeping them as is, to join the tables? When working from flat files I have always built my facts and dimensions this way. 


Will the extra steps in the editor slow the model down, is this worth it for the storage in pbix file? 

 

Any advice on if this is worth undertaking with a pefectly good data source and set of keys and ID's please. 

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

Hi  @DemoFour ,

Power Query is very aggressive about reloading the query preview. It seems that each additional step can result in re-reading the source and re-applying intermediate steps. If you have expensive query steps or a large data source, I've seen this slowdown. With well-optimized transformations and sufficient disk throughput, I don't see this sort of slowdown.

 

Also try these two steps to speed things up in power query:

1)  Unchecked the "Allow data preview to download in the background."  Make sure that this is unchecked in the report view and query editor.

2)  The other option that I unchecked was "Enable refresh of this query."  This is located in the Query Editor under Properties.  DO NOT UNCHECK "Enable load to report."....you will lose the report.

 

You can check this link, which has "Data reduction techniques for Import modeling", for example, Remove unnecessary columns, Remove unnecessary rows

https://docs.microsoft.com/en-us/power-bi/guidance/import-modeling-data-reduction

https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

 

Best Regards,

Liu Yang

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

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @DemoFour ,

Power Query is very aggressive about reloading the query preview. It seems that each additional step can result in re-reading the source and re-applying intermediate steps. If you have expensive query steps or a large data source, I've seen this slowdown. With well-optimized transformations and sufficient disk throughput, I don't see this sort of slowdown.

 

Also try these two steps to speed things up in power query:

1)  Unchecked the "Allow data preview to download in the background."  Make sure that this is unchecked in the report view and query editor.

2)  The other option that I unchecked was "Enable refresh of this query."  This is located in the Query Editor under Properties.  DO NOT UNCHECK "Enable load to report."....you will lose the report.

 

You can check this link, which has "Data reduction techniques for Import modeling", for example, Remove unnecessary columns, Remove unnecessary rows

https://docs.microsoft.com/en-us/power-bi/guidance/import-modeling-data-reduction

https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

 

Best Regards,

Liu Yang

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

@v-yangliu-msft 

Hay, thank you for taking the time to reply and signpost me on the the resources. 

 

Kind regards,

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.