I won't be able to help as I don't have access to Office Script :(
Just wanted to advise you post - at least - your script if you expect some help...
The sorting is OK in the Power Query editor but not on the worksheet it's working. I can't explain why but here is how I fixed the issue:
Click somewhere in the query ouput table
Go to Data tab > Load To > choose Only Create Connection > OK > OK
Select cell B5
Go to Data tab > Queries & Connections > Right-click on query FileNamesInColumns > Load To... > choose Table > OK
Good on my side:
Conditionally SUMing filtered data is always quite a challenge with formulas. Could you:
- updload & share (eg. with OneDrive) a representative workbook (remove sensitive info. if any)
- post a screenshot showing the expected result
- clarify which version of Excel is in use + on which OS (Windows/Mac)
- confirm formulas is the only way to go. In other words are you/they open to other options eg. Pivot Table or something else
Thanks
Take your time to check the query does what you want....
In the meantime I found another approach that doesn't require to do a DIR in a command prompt + I improved the perf. of the queries
Could you please tell me how did you apply these filters? step-by-step
I'm afraid to say that this isn't realistic. Power Query as any other techno. isn't something you learn nor teach in a few minutes
I have taken your 3 different patterns (Sublist, Amongst Friends, 300 most important French adjectives) and built a new query that should do what you want assuming files are in different folders. There's no more need to generate a .txt file with a DIR from the command prompt, the query picks the .mp3 files that exist in the Folder path:
There's another parameter (Number of output columns) for you to use...
New query is avaible here for download
On the question regarding formatting- All the sheets are formatted the same with info and data table in the same positions on the sheets
OK, so do you want a PowerBI or an Excel/Power Query example?
OMG. The site bugs. My last reply (PowerBI = Power Query) doesn't appear anymore. Same goes for your reply to it :-((((
Please re-post your most recent reply as an Answer below this one, not as a Comments
@RobRayborn-5123. To help @emilyhua-msft help you, could you explain how you come to a Grand Total of 7.65 for column [Price]???
According to your picture and the formula you initially posted 3 rows (the one you bolded) qualify for a calculated total at the row level
Reproed in Excel below this gives me a Grand Total of 189.46 (+ not quite sure where your 3 values come from & 2 should be negative, or maybe it's my version of Excel...)
In D2 (and down):
=([@[Avg LE Price]]-[@[PY Avg LE Price]]) * [@[Part Qty]]
In D5:
=SUM([Price])