Learn how to generate the letters A to Z with Excel Worksheet Formulas and Power Query.
Download Excel File: https://excelisfun.net/files/SEMT00016.xlsx
#excel #excelisfun #analytics #microsoftexcel #microsoftmvp, #short #shorts #excelmvp #excelformulas #excelfunctions #excelformulaforjobinterview #excelforbegginers #excelshorts #exceltutorial #exceltips #exceltipstricks #freeclass #freecourse #freeclasses #freeexcelcourse #freeexcel #shortsyoutube #shorts #short
#alphabet #text
Download Zipped Folder with All Files: https://excelisfun.net/files/22-M365ExcelClassVideoFiles.zip
Download pdf notes: https://excelisfun.net/files/22-M365ExcelClassDAX.pdf
This video teaches how to create reports when you have two date columns in a Fact Table Three ways: Worksheet Formulas, DAX Data Model Formulas and Power Query. Learn about how to create a Side By Side Report and a Cross Tabulated report for ordered sales and shipped sales. Learn about USERELATIONSHIP DAX Function, and how to create two date dimension tables using Power Query in the Data Model. See solutions in both Power BI Desktop and Excel Power Pivot.
This full free Microsoft 365 Excel Continue reading “Two Date Columns in Fact Table? How To Create Report? DAX, Power Query, Excel? 365 MECS Class 22”
A frequent question I have received is “How do I add the last refresh date and time to Power BI reporting?”. Great news, this solution is very simple! The code I provide in this article will allow you to create a source, and then use this in one or more of your visuals so that it’s always clear to you and your end users when the report was last refreshed.
Note: if you need refresh times for your individual sources, one solution would be to add a column with DateTime.LocalNow() and then work a DAX measure off of that once the data from that source is loaded for the first time. Definitely comment below if you would like me to expand on that.
Adding the Refresh Source
You are simply going to add a new source, choose Blank from the menu.
Click Advanced Editor to open up the M code window, then copy in the code below.
|
1 2 3 4 5 6 7 8 |
let Source = #table(type table[Date Last Refreshed=datetime], {{DateTime.LocalNow()}}), #"Added Custom" = Table.AddColumn(Source, "Updated", each "Updated "), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Updated]&Text.From([Date Last Refreshed])), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Date Last Refreshed", "Updated"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "LastUpdate"}}) in #"Renamed Columns1" |
Click Done. You will notice that I have added the word “Updated” ahead of the date and time in this code. I prefer to display the measure this way in my reporting. If you would prefer to just have the date and time, simply delete the steps after Source in the Query Settings at the right. I find it’s best to work from the bottom, up when removing steps.
Of course, you can also edit the output as you see fit, displaying in the way that works best for your report.
After Applying the change, you should now see the source and column listed.
I find it easiest to add a card visual to display the date and time. Simply select the card visual and then select the LastUpdate column. You can then edit the visual as you see fit for your report.
As always, I hope this post has helped you out. Please subscribe to be notified of the latest posts and leave a comment if you have any topics you’d like to see covered here. Thanks!
Topics covered in this post: Power Query Last Refresh Date and Time, Power Query M Current Date and Time, Last Refresh Date and Time in Power BI
Download Excel File: https://excelisfun.net/files/21-M365ExcelClass.xlsx
Pdf notes: https://excelisfun.net/files/21-M365ExcelClass.pdf
Learn how to build Mortgage Loan Schedule to break out interest and principal reduction from a loan payment. Theis same schedule works on Corporate Bonds to help record Interest on Income Statement6 and Principal Reduction on Balance Sheet.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
This video covers.
1. (00:00) Introduction and video topics
2. (00:30) Build Mortgage Loan Schedule to break out interest and principal reduction from a loan payment using Continue reading “Build Mortgage Home Loan Schedule. Traditional &Dynamic Spilled Array Formulas. 365 MECS Class 21”
The Association for Professionals in Infection Control and Epidemiology is holding their annual conference in Orlando June 26-28, 2023. I will be presenting a half-day pre-conference session on Sunday June 25, 2023 from 8 AM to 1 PM.
My goal in the session is to make you more comfortable with Microsoft Excel. In this preview, I download data from the CDC, do some simple clean-up. I use a few Excel formulas to add some useful metrics. I then display the results on a Map in Microsoft Excel.
Everyone who attends my session receives a copy of my book, MrExcel 2021 – Unmasking Continue reading “Excel For The Infection Preventionist – Preview of APIC Pre-Conference”
Learn how to create a formula to sort a column of full names with an Excel Formula. Some names have no middle name others have a middle name.
Download Excel File: https://excelisfun.net/files/SEMT00015.xlsx
#excel #excelisfun #analytics #microsoftexcel #microsoftmvp, #short #shorts #excelmvp #excelformulas #excelfunctions #excelformulaforjobinterview #excelforbegginers #excelshorts #exceltutorial #exceltips #exceltipstricks #freeclass #freecourse #freeclasses #freeexcelcourse #freeexcel #shortsyoutube #shorts #short #Sort #sortby #SortNames
Download Excel File: https://excelisfun.net/files/EMT1821.xlsx
Learn how to conditionally format a row in a table when the employee is listed more than one time for a specified Date. Learn how to create Logical Formula to apply Formatting. See COUNTIFS function.
Topics:
1. (00:00) Introduction
2. (00:26) Learn how to conditionally format a row in a table when the employee is listed more than one time for a specified Date. Learn how to create Logical Formula to apply Formatting. See COUNTIFS function.
3. (02:47) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #microsoftexcel #microsoftmvp #conditionalformating #conditionalformatting #conditionalformattinginexcel
Download Excel File: https://excelisfun.net/files/20-M365ExcelClass.xlsx
Pdf notes: https://excelisfun.net/files/20-M365ExcelClass.pdf
Learn how to build a Bank CD worksheet schedule to show monthly interest and final CD Value. Learn about the financial concepts behind CD value, and the more general investment future value. Learn about the math that makes compound interest so powerful. See two solutions: worksheet formula model and dynamic spilled array formula model.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
This video covers.
1. (00:00) Introduction and video topics
2. (00:31) Continue reading “What Is My CD Worth? A Lesson in Finance & Worksheet Model Building. 365 MECS Class 20”
Download Excel File: https://excelisfun.net/files/EMT1821.xlsx
Learn about how to sort a column of full names (including middle names) by last name using an Excel Formula. See 4 different methods, including Microsoft 365 Excel methods and methods that will work in older versions of Excel.
Topics:
1. (00:00) Introduction
2. (00:21)
3. (00:37) Microsoft 365 Excel Formula (Easy One), SORTBY, TEXTAFTER, LEN and SUBSTITUTE functions.
4. (01:57) Crazy M 365 Excel Formula #1. LOOKUP, BYROWS, LAMBDA, TEXTSPLIT, SORTBY functions with BIG Text Lookup Value “zzzzzz”.
5. (03:57) Crazy M 365 Excel Formula #2. Same as #2, but using BIG Text Lookup Value “Ω”.
6. (04:31) Old School Continue reading “Excel Formula to Sort By Last Name: Four Awesome Examples! Excel Magic Trick 1821”
Microsoft Excel Tutorial: Use Excel VBA to replicate the IMAGE function for images stored locally.
In episode 2606, I showed the new Insert Pictures in Cells and lamented that they did not change the IMAGE function to allow images stored on the local hard drive. Using the VBA Macro in this workbook, you can do it. The VBA is actually better because it remembers the image path and displays it in the formula bar.
To download the workbook or copy the VBA: https://www.mrexcel.com/youtube/0NArag0pV7I/
Table of Contents
(0:00) Enhancing Picture In Cells to use a formula
(0:30) Behind Scenes of 2606
(1:00) Sort Order Continue reading “Excel Place Local Pictures In Cell Using Formula and a VBA Hack – Episode 2607a”
Microsoft Excel Tutorial – new feature to place a picture in a cell in Excel.
Can the Excel IMAGE function use a local hard drive? Not yet.
New Excel VBA method: InsertPictureInCell
Insert Many Pictures in Excel in one command.
Feature Requests:
Expose a VBA property to show original source of the image
Allow IMAGE function to point to local hard drive.
Allow Power Query to load in-cell images.
To download this workbook: https://www.mrexcel.com/youtube/mFd8HsHmfpA/
Table of Contents
(0:00) New Picture In Cell feature
(0:33) Pictures on drawing layer
(0:47) Inserting image in cell in Excel
(1:05) Fitting in Cell
(1:20) Alignment
(1:35) No apparent way to rotate image
(2:02) Use Case from Jerry
(3:48) Continue reading “Excel – Picture Place In Cell – Episode 2606”
Download Zipped Folder with All Files: https://excelisfun.net/files/19-M365ExcelClassVideoFiles.zip
50 pages of pdf notes: https://excelisfun.net/files/19-M365ExcelClass.pdf
This video shows how to create an actual vs. budget variance report in three ways: 1) Relationships and DAX Formulas, TREATAS Dax Function and Worksheet Formulas.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
This video covers.
1. (00:00) Introduction and video topics
2. (00:32) Budget Vs Actual Grain Problem
3. (01:13) Two Fact Tables
4. (02:20) Create Budget Vs Actual Variance Report with three methods
5. (02:20) Worksheet Formulas Continue reading “Budget Vs. Actual Variance Analysis: Data Modeling, DAX, or Worksheet Formulas? 365 MECS Class 19”