Skip to content

Instantly share code, notes, and snippets.

View aabundez's full-sized avatar

Angel Abundez aabundez

9 followers · 3 following
View GitHub Profile
@aabundez
aabundez / Get-AADGroupMembers.ps1
Last active September 18, 2023 23:40
This Powershell script lists all of the members of an Azure AD group
<#
.DESCRIPTION
This script returns the group members for a single group.
.LINK
https://learn.microsoft.com/en-us/powershell/module/azuread/get-azureadgroup?view=azureadps-2.0
https://learn.microsoft.com/en-us/powershell/module/azuread/get-azureadgroupmember?view=azureadps-2.0
#>
@aabundez
aabundez / azsql_running_queries.sql
Created March 3, 2022 18:23
What queries are running in Azure SQL Database?
SELECT r.session_id
,s.login_name
,c.client_net_address
,s.host_name
,s.program_name
,st.TEXT
,s.STATUS
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c ON r.session_id = c.session_id
@aabundez
aabundez / TMSCHEMA_COLUMNS-ExplicitDataType.txt
Last active April 15, 2021 03:10
ExplicitDataType codes in $SYSTEM.TMSCHEMA_COLUMNS
1 - (Calculated Column, so look at InferredDataType column)
2 - Text
6 - Whole Number
8 - Decimal Number
9 - Date
11 - Boolean
//https://docs.microsoft.com/en-us/analysis-services/tabular-models/data-types-supported-ssas-tabular?view=asallproducts-allversions
@aabundez
aabundez / azsql_table_size.sql
Created March 14, 2021 20:09
Azure SQL Table Size + Properties
--Table size and properties
SELECT schema_name(t.schema_id) + '.' + t.name AS [table]
,p.[rows]
,cast(sum(spc.used_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS used_mb
,cast(sum(spc.total_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS allocated_mb
,p.index_id
,p.data_compression_desc AS [Index Data Compression]
,t.create_date
,t.lock_on_bulk_load
,t.lock_escalation_desc
@aabundez
aabundez / data_last_refreshed.pq
Created January 20, 2021 04:31
PQ for Data Last Refreshed
let
Source = #table(type table[Date Last Refreshed=datetime], {{DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-8)}}), //PST is UTC-8
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Last Refreshed", type datetimezone}})
in
#"Changed Type"
@aabundez
aabundez / Get-AADUserInfo.ps1
Created October 28, 2020 17:57
Powershell to extract AAD user info based on a list of AAD user IDs.
<#
.DESCRIPTION
This script imports a list of UPN's and extracts more user information from Azure Active Directory via the AzureAD PS module.
.LINK
https://docs.microsoft.com/en-us/powershell/module/azuread/get-azureaduser?view=azureadps-2.0
#>
$upns = import-csv "C:\Directory\File.csv" # Replace filepath here
@aabundez
aabundez / quick_prior_month_sales.dax
Created November 24, 2019 20:11
Prior period DAX calculation using automatic time intelligence in Power BI
Prior Month Sales = CALCULATE ( SUM( Sales[Revenue] ), PREVIOUSMONTH( Sales[Date].[Date] ) )
// Quick measure uses DATEADD function instead
Prior Month Sales = CALCULATE ( SUM( Sales[Revenue] ), DATEADD( Sales[Date].[Date], -1, MONTH ) )
@aabundez
aabundez / Prior_Month_Sales.dax
Last active November 22, 2019 16:22
Calculate prior month sales without a Date table
Revenue PM =
var prior_month_start_date = EOMONTH( MIN ( Sales[Date] ), -2 ) + 1
var prior_month_end_date = EOMONTH( MIN ( Sales[Date] ), -1 ) + 1
RETURN
CALCULATE(
SUM ( Sales[Revenue] ),
FILTER (
ALLSELECTED ( Sales ),
Sales[Date] >= prior_month_start_date && Sales[Date] < prior_month_end_date
)
@aabundez
aabundez / DIAD_DAX_Samples.txt
Last active November 21, 2019 06:32
//Date Table
Date = CALENDAR (DATE(2012,1,1), DATE(2018,12,31))
Date = CALENDAR ("1/1/2012", 12/31/2018")
//Time Intelligence function
PY Sales = CALCULATE ( SUM ( Sales[Revenue] ), SAMEPERIODLASTYEAR( 'Date'[Date] ) )
PM Sales = CALCULATE ( SUM ( Sales[Revenue] ), PREVIOUSMONTH( 'Date'[Date] ) )
//Ratios
% Growth = DIVIDE(SUM(Sales[Revenue])-[PY Sales],[PY Sales])
@aabundez
aabundez / Calendar.pq
Last active July 2, 2019 22:18
This Power query invokes fn_Calendar.pq (https://gist.github.com/aabundez/06f6d3517631ed9e6ad05e8c00e79ba9)
let
Source = fn_Calendar(#date(2018, 8, 1), Date.From(DateTime.FixedLocalNow()), null)
in
Source