•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How Can I Use Connection-Only Quieries?
Our take
Connection-only queries in Power Query can enhance your data management by allowing you to reference data without loading it directly into your workbook. This approach is particularly beneficial when imitating an ERP system, as it can significantly improve performance by reducing the load on your workbook. Instead of loading every query, consider using formulas like XLOOKUP to access the necessary data dynamically. This method not only streamlines your processes but also empowers you to maintain efficiency while exploring innovative solutions in your data workflows.
We are imitatng an ERP system using Power Query, and the accreditation of queries on top of queries is starting to impact performance. I think I can speed it up by not loading ever query into the workbook.
Is there a way to reference data via formula (I.e XLOOKUP) in a worksheet without loading the quiered data into the workbook?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How Do I Speed Up My PowerQueryMy powerquery is so slow even though I tried to make it faster. The data In working with is just the raw data and I’m tasked with converting everything into something else through custom columns. These custom columns use legends by merges and are nested if statements. The excel equivalent would be vlookups with nested ifs. I made a library inside Powerquery that houses that logic. I also branched the query into 2, one where the columns aren’t used in the custom column creation and another where it is. I know this decrease the amount of data since the columns went from 20s to 7-8. The issue is the data doesn’t load. And I didn’t even remerge the constant with the custom columns throigh an index column, then append everything. I tried Buffering the alleged which didn’t do a thing submitted by /u/Lucky-Tea-2370 [link] [comments]
- Excel Performance optimisation tips!Working in demand planning I have got it the point where I am making some pretty advanced files using a suite of techniques. My files often have lots of rows, with lots of Columns of complex formula including with sumifs, xloopup, ifs & Let. I’ve not advanced to using tables regularly though as I find the constraints & syntax annoying but am trying to get there & have started using power query to blend data for output analysis. The problem I am encountering is I filter ALOT drilling down into product groups etc, & excel tends to ‘hang’ a lot with ‘Not Responding’. Now I’m not sure it’s due to an underpowered machine (intel core i7 HP Elitebook) or, more likely lots of complex formula referencing ranges or tables. My question to the hive brain: share your optimisation tips & tricks! -Can Lamda combined with Let speed things up? -Are Tables vital to speeding up complex sumifs & lookups? - are match helper columns combined with Index leaner & faster than xlookup? Hit me with best tips & tricks! submitted by /u/NZGRAVELDAD [link] [comments]
- Two workbooks, two columns in second book could include matching data, neatest most efficient way to query between them on multiple fields using excel formula only, no VBA / Power Query etc?Not even sure how to surmise this but here we go: Workbook 1, column i has a reference code, "EntID" which could be a number OR text. Workbook 2, that reference code might be found in Column A, or Column B, or neither. In Workbook 1, I need 5 lookup columns to pull data from 5 corresponding columns in Workbook 2 where there is a match for " EntID" in EITHER column A or B. If there is no match then the cell should state "Not Found". I have it working by using nested if statements, isnumber & xmatch for the first lookup column to ascertain where the match is found and then xlookups on remaining columns depending on the result returned to that cell but the formula are huge if for no other reason than the file naming and locations (which cannot be changed) and it feels slow, clunky and unrefined. How would you do it? submitted by /u/Delinquent90 [link] [comments]
- Get more from Power Query in Excel with these little-known capabilitiesIf you use Excel or Power BI to work with data, you’ve likely encountered Power Query - and may already rely on it regularly across desktop and web. It pulls data together, cleans it up, and prepares it for analysis. But beyond those familiar tasks, Power Query includes several capabilities that can make solutions more flexible, scalable, and easier to maintain. Get more from Power Query in Excel with these little-known capabilities submitted by /u/beyphy [link] [comments]
Tagged with
#Excel alternatives for data analysis#big data performance#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#data analysis tools#data cleaning solutions#rows.com#AI formula generation techniques#Excel compatibility#formula generator#Excel alternatives#Connection-Only Queries#Power Query#ERP system#performance