IIF([Measures].[Time Spent (mins)] = NULL, NULL, IIF( [Measures].[Time Spent (mins)] < 60 ,CStr([Measures].[Time Spent (mins)]) + " mins" ,CStr(Int([Measures].[Time Spent (mins)]/60)) + " hours, " + CStr(([Measures].[Time Spent (mins)] - (Int([Measures].[Time Spent (mins)]/60)*60))) + " mins" ) )
Matt Smith matt40k
matt40k
/ CastAs.sql
Created
December 5, 2016 15:15
Oh, change the old source format will you. Well fine, I'll throw a view over the top with some basic casting
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select | |
| '[' + c.name + '] = cast('+c.name+' as ' + ty.name + case when ty.name = 'varchar' then '(' + cast(c.[max_length] as varchar(4)) + '))' else ')' end | |
| from | |
| sys.[all_columns] c inner join sys.tables t on c.[object_id] = t.[object_id] inner join sys.types ty on c.[system_type_id] = ty.[system_type_id] where t.name = 'TABLE' and schema_name(t.schema_id) = 'SCHEMA' | |
| order by | |
| c.[column_id] asc |
matt40k
/ JustSomeNotes.txt
Last active
December 4, 2016 23:26
Random notes about custom viz in PowerBi
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| NodeJs | |
| ====== | |
| https://nodejs.org/dist/latest-v5.x/node-v5.12.0-x64.msi | |
| NOTE: The debug visual is currently only available in the PowerBI Service (not in desktop or mobile). <-- seriously? | |
| https://github.com/Microsoft/PowerBI-visuals/blob/master/VisualProject.md | |
| https://github.com/Microsoft/PowerBI-visuals-sampleBarChart |
matt40k
/ PrettyTime.mdx
Created
November 30, 2016 21:50
Pretty time - turns mins into hours and mins
matt40k
/ GetEduBaseData.ps1
Created
November 29, 2016 23:36
Hits API that returns the download url for csv data dump the DfE provide
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $url = 'https://getedubaseurl.apphb.com/api/Product/' | |
| $r = Invoke-WebRequest $url | |
| Write-Host $r.Content |
matt40k
/ sp_kickoff_dependent_jobs.sql
Created
November 23, 2016 15:16
sp_kickoff_dependent_jobs - https://saldeloera.wordpress.com/2012/01/31/metadata-driven-etl-dependent-job-execution/
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* ======================================================================= | |
| Author: Sal De Loera | |
| Create date: 1/31/2012 | |
| Description: Kicks off available SQL Agent Jobs flagged as ‘Ready’ in dbo.ETL_OBJ.ETL_STATUS_ID | |
| Input: N/A | |
| Output: execute applicable job | |
| ========================================================================= */ | |
| SET ANSI_NULLS ON | |
| GO | |
| SET QUOTED_IDENTIFIER ON |
matt40k
/ Sp_activate_dependent_job_status.sql
Created
November 23, 2016 15:15
Sp_activate_dependent_job_status - from https://saldeloera.wordpress.com/2012/01/31/metadata-driven-etl-dependent-job-execution/
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* ======================================================================= | |
| Author: Sal De Loera | |
| Create date: 1/31/2012 | |
| Description: Checks for parent jobs that have completed since the last run of the child. | |
| Input: N/A | |
| Output: Set the ETL_OBJ.STATUS_ID to ‘Ready’ status | |
| ========================================================================= */ | |
| SET ANSI_NULLS ON | |
| GO | |
| SET QUOTED_IDENTIFIER ON |
matt40k
/ mergeCSVFiles.ps1
Created
November 17, 2016 18:58
— forked from conrjac/mergeCSVFiles.ps1
Powershell Script to merge multiple CSV files into one (by appending each file to the end)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $folder = 'C:\Data' #INPUT FOLDER PATH - e.g C:\Data | |
| $files = Get-ChildItem $folder\*.csv -Exclude $folder\MergedCsvFile.csv | |
| Get-Content $files | Set-Content $folder\MergedCsvFile.csv |
matt40k
/ DistributionLists.M
Last active
November 15, 2016 23:13
PowerQuery (M) code for querying Active Directory for Distribution lists
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let | |
| Source = ActiveDirectory.Domains("domain.local"), | |
| #"YourDomain" = Source{[Domain="domain.local"]}[Object Categories], | |
| group = #"YourDomain"{[Category="group"]}[Objects], | |
| #"Expanded group" = Table.ExpandRecordColumn(group, "group", {"mail", "managedBy", "member"}, {"group.mail", "group.managedBy", "group.member"}), | |
| #"Expanded mailRecipient" = Table.ExpandRecordColumn(#"Expanded group", "mailRecipient", {"dLMemSubmitPerms"}, {"mailRecipient.dLMemSubmitPerms"}), | |
| #"Expanded mailRecipient.dLMemSubmitPerms" = Table.ExpandListColumn(#"Expanded mailRecipient", "mailRecipient.dLMemSubmitPerms"), | |
| #"Expanded group.member" = Table.ExpandListColumn(#"Expanded mailRecipient.dLMemSubmitPerms", "group.member"), | |
| #"Filtered Rows to only Groups with Email addresses" = Table.SelectRows(#"Expanded group.member", each [group.mail] <> null), | |
| #"Expanded group.managedBy" = Table.ExpandRecordColumn(#"Filtered Rows to only Groups with Email addresses", "group.managedBy", {"mail"}, {"group.managed |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $folder = "C:\Users\MattSmith\OneDrive - Matt40k\Email" | |
| $files = Get-ChildItem -Path $folder | |
| foreach ($file in $files) { | |
| #Write-Host $file | |
| $oldName = "$folder\$file" | |
| $newName = "$folder\$file.csv" | |
| Rename-Item -Path $oldName -NewName $newName | |
| } |
matt40k
/ cqc.ps1
Last active
July 7, 2020 10:30
Care Quality Commission (CQC) - Basic PowerShell script to query Providers API (https://api.cqc.org.uk/public/v1/)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $url = 'https://api.cqc.org.uk/public/v1/' | |
| $proxy = 'http://proxy:8000' | |
| $limit = '1000000' | |
| #$limit = '5' | |
| $name = 'partnerCode={{ YOUR COMPANY NAME \ CODE }}' | |
| #$limit = '5' | |
| $providersUrl = $url+"providers?perPage=$limit&$name" | |
| Write-Host $providersUrl | |
| $r = Invoke-WebRequest -Uri $providersUrl -Method GET -ProxyUseDefaultCredentials -Proxy $proxy | |
| $items = $r.Content | ConvertFrom-Json |