One use case I have found for performing merges in Source is when the task is resource intensive -- multiple merges of selected columns from large tables in serial. I had one that refused to finish when each select columns and merge was an individual code line, failed in Service and Desktop. Okay, maybe my environment but when I nested multiple merges and select columns as Source it ran like poop through a goose.
If excel had the flag for “enable load” or “include in refresh“ I might use this. On the surface this looks like too many queries and connections to manage
Hi there! In Excel using Power Query, when you choose "close and load to". Check the "only create connection" option. This is the same as unchecking enable load in Power BI :)
@@HavensConsulting yea that’s why I mentioned “queries and connections “. This is the area that is going to be visually hard to manage, compared to the functionality in power bi.
@@cathrerinezetadrones3169 even with adding groups to your queries in that list. It'd be too hard to manage? You can put any disabled queries in a group for "Staging Queries" and collapse that. I follow the same Extact, Transform, and Load top level groupings for my queries both in Power BI, and Excel PowerPivot queries.
@@HavensConsultingyes, I understand the capabilities. In pbi I would never load staging tables to my model as it increases the storage in vertipaq. If excel had the option to hide staging tables it would show the end user a more direct correlation between the visible data and the source.
@@cathrerinezetadrones3169 I think I'm still misunderstanding your situation then. As with Excel you can also not load staging tables to your model, if you only use the create connection only option. In both Power BI and Excel, you can have 10 queries, 5 that are loaded to model, and in both scenarios you'd see the 10 queries when you looked at your queries list, but the 5 staging queries would not add any storage for vertipaq in either Excel or Power BI. Their storage cost would be the same
One use case I have found for performing merges in Source is when the task is resource intensive -- multiple merges of selected columns from large tables in serial. I had one that refused to finish when each select columns and merge was an individual code line, failed in Service and Desktop. Okay, maybe my environment but when I nested multiple merges and select columns as Source it ran like poop through a goose.
Great tips! Thanks for sharing Ken and Reid!
Our pleasure!
excellent
If excel had the flag for “enable load” or “include in refresh“ I might use this. On the surface this looks like too many queries and connections to manage
Hi there! In Excel using Power Query, when you choose "close and load to". Check the "only create connection" option. This is the same as unchecking enable load in Power BI :)
@@HavensConsulting yea that’s why I mentioned “queries and connections “. This is the area that is going to be visually hard to manage, compared to the functionality in power bi.
@@cathrerinezetadrones3169 even with adding groups to your queries in that list. It'd be too hard to manage? You can put any disabled queries in a group for "Staging Queries" and collapse that. I follow the same Extact, Transform, and Load top level groupings for my queries both in Power BI, and Excel PowerPivot queries.
@@HavensConsultingyes, I understand the capabilities. In pbi I would never load staging tables to my model as it increases the storage in vertipaq. If excel had the option to hide staging tables it would show the end user a more direct correlation between the visible data and the source.
@@cathrerinezetadrones3169 I think I'm still misunderstanding your situation then. As with Excel you can also not load staging tables to your model, if you only use the create connection only option. In both Power BI and Excel, you can have 10 queries, 5 that are loaded to model, and in both scenarios you'd see the 10 queries when you looked at your queries list, but the 5 staging queries would not add any storage for vertipaq in either Excel or Power BI. Their storage cost would be the same