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

[DemoFour]

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. 

[v-yangliu-msft · Accepted solution]

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.

[DemoFour]

@v-yangliu-msft 

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

Kind regards,