Uploaded November 2024 | Updated November 2026, 3 hours ago
👩🏻🎓 Check out my Professional Excel Productivity & Power BI courses
accessanalytic.thinkific.com
An awesome simple explanation of HOW TO BUILD a robust multi row dependent drop down list in Excel
I realised after recording that Mark suggested this exact solution at the start of our UNPIVOT podcast and I didn't realise until listening back to the podcast a few days later.
Hear the discussion youtu.be/OFNDtXxrk0g
⏬ Download the file:
aasolutions.sharepoint.com/:f:/s/PubliclyAvailableContent/Es1QX19t3ElLmyoMq_hntlwBDLD_D97_7ztOlRPVfSTqMQ?e=buxjaM
Mark’s video
youtu.be/L3jWMoE6KnI?si=b5HxJRYcZBAciU37
My previous video of “easiest dependent drop down list”
youtu.be/U3WnM2JCrVc?si=ldYgW3oatoJt-0to
00:00 the aim
00:48 table set up
02:13 range name set up
04:00 the magic XLOOKUP : XLOOKUP
09:00 conditional formatting to flag invalid choices
13:43 if you can’t rely on a sorted table
🧑🏻🎓Check out our Power BI Training Course
accessanalytic.thinkific.com
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
https://pbi.guide/book/
Connect with me
wyn.bio.link
👩🏻🎓 Check out my Professional Excel Productivity & Power BI courses
accessanalytic.thinkific.com
An awesome simple explanation of HOW TO BUILD a robust multi row dependent drop down list in Excel
I realised after recording that Mark suggested this exact solution at the start of our UNPIVOT podcast and I didn't realise until listening back to the podcast a few days later.
Hear the discussion youtu.be/OFNDtXxrk0g
⏬ Download the file:
aasolutions.sharepoint.com/:f:/s/PubliclyAvailableContent/Es1QX19t3ElLmyoMq_hntlwBDLD_D97_7ztOlRPVfSTqMQ?e=buxjaM
Mark’s video
youtu.be/L3jWMoE6KnI?si=b5HxJRYcZBAciU37
My previous video of “easiest dependent drop down list”
youtu.be/U3WnM2JCrVc?si=ldYgW3oatoJt-0to
00:00 the aim
00:48 table set up
02:13 range name set up
04:00 the magic XLOOKUP : XLOOKUP
09:00 conditional formatting to flag invalid choices
13:43 if you can’t rely on a sorted table
🧑🏻🎓Check out our Power BI Training Course
accessanalytic.thinkific.com
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
https://pbi.guide/book/
Connect with me
wyn.bio.link
![Visual Calculations in Power BI
⚡⚡⚡More information in the description below⚡⚡⚡
A first look at Visual Level Calculations in the February 2024 release of Power BI Desktop. Looks very promising, should simplify a lot of common DAX requirements.
⚠️ Formatting Update:
Theres new formatting options as of August 2024
https://youtu.be/a6lI_B58wqQ?si=t0ldp_zZUb5B06Oy
For more format options see https://learn.microsoft.com/en-au/power-bi/create-reports/desktop-custom-format-strings?WT.mc_id=M365-MVP-5002589
If you need to do further calculations on a field AND you want to display it formatted then you will need to create a duplicate unformatted column and hide the original non formatted column that is used in further calculations.
At this stage you also cannot sort by a measure value (the RUNNINGSUM doesnt respect the sort order of a visual if its sorted by a measure) Instead youd need to do something like this
RUNNING TOTAL by Units= SUMX(WINDOW(1, ABS, 0, REL, ROWS, ORDERBY([Units], ASC)), [Units])
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/
Did you know Ive written a book Power BI for the Excel Analyst?
https://pbi.guide/book/
Connect with me
https://wyn.bio.link/ Visual Calculations in Power BI](https://i.ytimg.com/vi/myfQK40yV-I/mqdefault.jpg)









![ULTIMATE Guide: Combine Files from OneDrive & SharePoint (2025 Update!)
Join the channel to get access to perks:
https://www.youtube.com/channel/UCvD2rJJrp0aS48-KCk0eMHQ/join
🤷🏻♂️365 BUSINESS
Sadly the button is not included in 365 for Business . However you can still use the code in a blank query
= SharePoint.Contents(YOURPATH/, [ApiVersion = 15])
This updated tutorial is your ultimate guide to consolidating multiple Excel files from a OneDrive or SharePoint folder using Excel or Power BI — even when your files contain different sheet names or have empty data. The video walks you through the new OneDrive link changes, explains how to connect with the right URL, and demonstrates how to future-proof your queries by referencing and transforming data dynamically. Youll also learn how to troubleshoot and fix common errors, such as missing sheet names or blank tables, by building a custom error-handling query to identify and isolate problematic files.
🧑🏻🎓Learning Power BI?
Check out our online course
https://accessanalytic.thinkific.com/
Did you know Ive written a book Power BI for the Excel Analyst?
https://pbi.guide/book/
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/ ULTIMATE Guide: Combine Files from OneDrive & SharePoint (2025 Update!)](https://i.ytimg.com/vi/psQRZQbOFPY/mqdefault.jpg)