Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
What would you say is the optimal set-up for the scenario below?
I have 10 customers (say). For each of those customers, there are three tables (all exactly the same structures) on separate tabs within a sharepoint excel workbook that my colleagues can work on.
I then merge/transform each customer's 3 tables to produce one final query per customer. The final queries per customer are then appended to produce my final dataset, ready for measures and visualisations.
Is this the right approach? Or, would it be better to first append each customer's A, B and C tables with each other and then merge/transform once, albeing on one much larger A, B and C table?
I look forward to receiving some guidance.
Solved! Go to Solution.
In my experience working with one Excel file with multiple tables of same structure, I generally get best performance by immediately expanding (and automatically combining) tables off the initial Excel.Workbook access function. Pre-reqs/steps:
I've found that this minimizes repeated queries to the data source, and makes maintenance easier (e.g. schema drift like when column name changes; can just fix in the one expand step rather than multiple places)
In my experience working with one Excel file with multiple tables of same structure, I generally get best performance by immediately expanding (and automatically combining) tables off the initial Excel.Workbook access function. Pre-reqs/steps:
I've found that this minimizes repeated queries to the data source, and makes maintenance easier (e.g. schema drift like when column name changes; can just fix in the one expand step rather than multiple places)
@MarkLaf I don't think I could get over to you how much this post has transformed my work! Thank you!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |