John Liu Blog

Every drop counts

Power BI Date Table

There are a few ways to create date table in Power BI. Often, we need to create a few new columns to help with visual sorting etc. In Model View, click New Table and then using one of the following DAX functions to create the table and other computed columns as needed. Using DAX: Calendar = CALENDARAUTO() //returns a contiguous, complete range of dates that are automatically determined from your dataset Calendar = CALENDAR(date(2010,1,1),date(2050,12,31)) //returns a contiguous range of dates based on a start and end date that are entered as arguments in the function Calendar = CALENDAR(MIN('table'[date]),MAX('table'[date])) //similar to CALENDARAUTO() MonthNum = MONTH(Dates[Date]) WeekNum = WEEKNUM(Dates[Date]) DayoftheWeek = FORMAT(Dates[Date], "DDDD") FYMonthNum = VAR FYStartMonth = 7 //Update the fiscal year starting month above *Use number between 1 to 12 RETURN IF ( MONTH ( 'Calendar'[Date] ) >= FYStartMonth, MONTH ('Calendar'[Date] ) - ( FYStartMonth - 1 ), 12 + ( MONTH ('Calendar'[Date] )- ( FYStartMonth - 1 )) ) FYYear = VAR FYStartMonth = 7 //Update the fiscal year starting month above *Use number between 1 to 12 RETURN IF ( MONTH ( 'Calendar'[Date] ) >= FYStartMonth, YEAR ('Calendar'[Date] ) +1, YEAR('Calendar'[Date] ) ) Month = FORMAT([Date], "MMM") Year = YEAR('Calendar'[Date]) Using Power Query (MDX) to generate a list and then use Transform to Convert the list To Table and format the column as Date.

Posted by John Liu Thursday, March 24, 2022

Automate email process

Note: When design Power Automate flow, for file path or folder path values, try not to pick from the dropdown list, but instead type in the actual name. When picking from the dropdown list, the designer will undercover save the actual internal physical ID of the location as well. If you then try to export the package and modify it for a different folder, it will be hard as the physical ID will no longer match with the one for the new location.

Posted by John Liu Monday, March 7, 2022

Reset Brother printer drum count

After replaced a new Brother printer tonner cartridge, the printer still displays “Replace Tonner” message. To get around this message: Open front cover of printer (to reveal the cartridge mechanism) and “Front Door Open” will flash on the display. Next, press STOP/EXIT and CLEAR at the SAME TIME (one quick press) Immediately after, press CLEAR once again. A menu called RESET MENU will appear. Arrow down to find your toner type TNR-STD Once you select the type of toner, PRESS OK Press the UP Arrow to confirm reset The Display should say “Accepted” Close the front cover Your printer should be ready to print!

Posted by John Liu Thursday, March 3, 2022

Find out the actual public IP address

When in a corporate or home network, run the following powershell command to find out the actual public IP address your computer exposed to the Internet. Make sure all VPN are closed first. (Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content Imports System.Net Module Module1 Sub Main() Dim client As New WebClient() Dim publicIp As String = client.DownloadString("https://api.ipify.org") Console.WriteLine($"Your public IP address is: {publicIp}") End Sub End Module Alternatively, you can simply browser to address https://ipinfo.

Posted by John Liu Wednesday, January 5, 2022

Disable Chrome incognito mode

To disable Google Chrome incognito mode, do the following in registry (create if not exist already): Under HKEY_LOCAL_MACHINE -> SOFTWARE -> Policies -> Google -> Chrome, create a DWord (32 bit) value key named IncognitoModeAvailability and set the value to be 1. This will disable the incognito mode. Set to 0 will enable it. Restart Google Chrome after setting change.

Posted by John Liu Thursday, December 30, 2021