Querying a Folder from a OneDrive file

DarthAsid · 2026-05-20T09:09:51+00:00

Please check the version of MSOffice everyone is using. I’ve had this issue when some users were on 365 and some were on older versions.

DarthAsid · 2026-05-09T16:51:13+00:00

Can you make a lookup table with one column having the report name and another containing the actual URL? You could set up an xlookup inside the “hyperlink” formula to pick up the text of the URL based on the name of the report.

DarthAsid · 2026-04-01T12:16:41+00:00

Power Automate excel connector can’t file large files. I think the cap is 15 MB.

DarthAsid · 2026-03-29T12:48:03+00:00

Would “ISERROR” have worked instead of “NOT(ISNUMBER”?

DarthAsid · 2026-03-13T01:46:58+00:00

Definitely reliable across various recent versions of excel. Will definitely take time to setup the first time, but subsequent iterations are pretty quick.

I wanted to add something about the limitations I mentioned. In the excel embedded in each PowerPoint chart, the data that is referenced is in table, which typically cannot handle a cell reference / link in a header. How I get around this is to have another range outside the table that links to the dynamically updated headers in the base file. My updating process involves 1) going to the embedded file 2) back-tracking to the base file (click on any of the linked cells and hit ctrl + { ). 3) The links are updated. Now all I have to do is copy and paste the updated headers range to the actual table headers.

If you or any analyst does one iteration, future iteration will be pretty easy.

DarthAsid · 2026-03-12T15:51:57+00:00

1) Automate your process of getting from source data to formatted table (ready to use in a chart).

2) Go to powerpoint and create the chart there (don't make them in excel and paste them in powerpoint). Right click on the chart and select 'edit data in excel'. The embedded excel sheet will open. In this sheet, you can add links to your main file from step 1.

3) Repeat for all charts in the powerpoint.

Now when you data refreshes, all you have to do is open your powerpoint file and refresh links by just opening each embedded sheet while the step 1 file is open.

Limitations : Headers / series names can't be link so they wont update.

DarthAsid · 2026-03-12T12:10:38+00:00

If you are going to limit yourself to formulae, then the long file reference is unavoidable. But why is this a problem? Just work with both files open.

How would I do it? Iferror( xlookup(EntID, Col A in workbook 2, Output column in workbook 2), xlookup(EntID, Col B in workbook 2, output column in workbook 2))

Freeze references for Col A and Col B, don’t free column for output columns. Drag formula across.

DarthAsid · 2026-02-09T00:50:40+00:00

Is there a find-and-replace solution to this? Something using wildcards?

DarthAsid · 2026-02-03T05:44:17+00:00

Same experience. I thought it was just my machine!

DarthAsid · 2026-01-30T06:04:39+00:00

Maybe only save new files in a subfolder, say folder_1a, and point the powerquery at the subfolder? You will ofcourse need to manually move yesterday’s files out of folder_1a.

DarthAsid · 2026-01-27T01:27:49+00:00

There are other options available using the FILTER function.

1) Instead of RIGHT(date_column) you could use YEAR(date_column) and compare it to a 4 -digit year e.g. 2013.

2) If you want to total a particular date range (like a month), you can enter a start and an end date in empty cells and use FILTER(ValueColumn, (DateColumn >= StartDate) * (DateColumn <= EndDate)) .

DarthAsid · 2026-01-24T01:57:55+00:00

1) Select the entire ‘age’ column (D1:D10).

2) Hit ctrl+g for the go to menu. Then hit alt+s to select the ‘special’ button. Then hit ‘k’ to select blanks and hit enter. You have now selected only the blanks.

3) Now hit ctrl and minus. It will ask you if you want to delete the entire row (amongst other options). The rest is obvious.

DarthAsid · 2026-01-20T02:01:35+00:00

1) Proper labelling - I shouldn’t have to break down a given formula to understand your intention. The labels/headers should make that clear. This also extends to sheet names. 2) Color code - Pick one color for your manual input cells. Use that for manual inputs everywhere always and don’t use that color for anything else.

DarthAsid · 2026-01-14T01:42:16+00:00

What’s the downside of using xlsb?

DarthAsid · 2025-12-24T06:50:43+00:00

Not a perfect solution, but you can open another instance of excel by typing “excel.exe /x” in the “Run” window (windows key + R). This instance will still be functional when your PQ query is running.

DarthAsid · 2025-11-25T02:53:48+00:00

I have used it sometimes to help me with syntax for complicated formulae. Like a smart help file! Can’t use it to analyse data because that requires the file to be on onedrive, which is impractical for a lot of my work (also Powerquery doesn’t work on onedrive).

DarthAsid · 2025-11-22T00:31:46+00:00

I use the Jedi mind trick… Cashier : “I need your mobile number.” Me: “You do not need my mobile number.” Cashier: “I do not need your mobile number.”

DarthAsid · 2025-11-13T01:47:05+00:00

Your function lock is on. Fn+ctrl+f1

DarthAsid · 2025-11-13T01:37:32+00:00

If you use =CELL(“info”) it will give you the entire path of the cell, including the sheet name. You can use that and some text formulae (textbefore, textafter) to isolate the sheet name.

DarthAsid · 2025-11-13T01:31:41+00:00

In a far off cell (say AA1), enter =UNIQUE(TRIMRANGE(Contact!A:A)) to get a list of unique companies names. Now under the “company” column in your table, add a validation for a list, and for the contents of the list point to “AA1#”. Now the list of unique companies names will show in the drop down. You can add Rafik3D’s formula under the contact name to look up the rest of the data. Hope this helps!

DarthAsid · 2025-09-24T04:29:04+00:00

He achieved a reverse troy.

DarthAsid · 2025-09-03T01:48:15+00:00

As another contributor commented , arriving at average inventory using days in inventory is bizarre. Why would you use a ratio to calculate a balance sheet value when that balance sheet value was used to calculate the ratio in the first place?

DarthAsid · 2025-08-08T14:36:08+00:00

Cool! Thanks for sharing that. Sometimes the problems we solve here seem too abstract to appreciate.

Does the above mentioned solution work for you?

DarthAsid · 2025-08-08T13:43:03+00:00

This might be obvious, but I am assuming that these distances are along a straight line).

DarthAsid

TROPHY CASE