Skip to content

Instantly share code, notes, and snippets.

Avatar

Imke Feldmann ImkeF

View GitHub Profile
@ImkeF
ImkeF / Table.ParentChildExplosion.pq
Last active June 23, 2022 06:48
Expands Parent-Child-Hierarchy (Basic Pattern).
View Table.ParentChildExplosion.pq
let
fnParentChild = let
func =
(ParentChildTable as table, ParentColumnName as text, ChildColumnName as text) =>
let
/* Debug Parameters
ParentChildTable = PCTable_,
ParentColumnName = "Parent_",
ChildColumnName = "Child_",
*/
@ImkeF
ImkeF / Tool.IdentifyUnusedMQueries.pq
Last active May 11, 2022 09:25
Identifies unused M-Queries that can be deleted
View Tool.IdentifyUnusedMQueries.pq
let
func = (ModelJsonPath as text) =>
let
// Helper function
fnRemoveBetweenDelimiters =
let
func = (
TextToClean as text,
StartDelimiter as text,
@ImkeF
ImkeF / Table.RemoveSPListColumns.pq
Last active October 16, 2020 08:21
Removes unnecessary columns from SharePoint List imports.
View Table.RemoveSPListColumns.pq
let func =
(SourceTable as table) => Table.RemoveColumns(
    SourceTable, 
    {
      "FileSystemObjectType", 
      "ServerRedirectedEmbedUri", 
      "ServerRedirectedEmbedUrl", 
      "ComplianceAssetId", 
      "OData__UIVersionString", 
      "GUID", 
@ImkeF
ImkeF / Xls.NORMINV.pq
Created June 18, 2020 06:42
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
View Xls.NORMINV.pq
let func =
(probability as number, mean as number, standard_dev as number) as number => 
    let
        // Source for NormalCDFInverse: https://www.johndcook.com/blog/normal_cdf_inverse/
        //StdDev = 1.5,
        //Mean = 2,
        p = probability,
        
RationalApproximation = (t as number) => 
            [c = {2.515517, 0.802853, 0.010328}, d = {1.432788, 0.189269, 0.001308}, return
View CC_GraphAPI_UriType.pq
section MyGraphB;
//
// OAuth configuration settings
//
// TODO: set AAD client ID value in the client_id file
client_id = Text.FromBinary(Extension.Contents("client_id"));
client_secret = Text.FromBinary(Extension.Contents("client_secret"));
redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";
View DoublingTime.dax
DoublingTime =
VAR CurrentDay =
MAX(DailyData[Date] )
// Get half of todays value
VAR ThresholdValue =
CALCULATE(MAX ( DailyData[Confirmed] ), DailyData[Date] = CurrentDay ) / 2
// Get the day when the number of cases was half as much as today
View TextTrimEndOptional_query.pq
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvL389AN0PXVNTRTitWJVnJ29NN18tF1NDQHc8GEhYVukK6js66hpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [product_name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"product_name", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.TrimEnd([product_name], {"0".."9"} & {"-"}))
in
#"Added Custom"
@ImkeF
ImkeF / Date.ToDateKey
Created November 19, 2019 07:45
Transforms and converts a <code>date</code> field to YYYYMMDD - text field.
View Date.ToDateKey
let func =
(DateToTransform as date) as text =>
Text.From(Date.Year(DateToTransform))
& Text.PadStart(Text.From(Date.Month(DateToTransform)),2,"0")
& Text.PadStart(Text.From(Date.Day(DateToTransform)),2,"0") ,
documentation = [
Documentation.Name = " Date.ToDateKey ",
Documentation.Description = " Transforms and converts a <code>date</code> field to YYYYMMDD - text field. ",
Documentation.LongDescription = " Transforms and converts a <code>date</code> field to YYYYMMDD - text field. ",
@ImkeF
ImkeF / Type.FromText
Last active October 5, 2021 08:14
Returns a type from its textual representation.
View Type.FromText
let func =
(TypeAsText as text) =>
Record.Field(
[type null = Expression.Evaluate( "type null", [ type null = type null] ),
type logical = Expression.Evaluate( "type logical", [ type logical = type logical] ),
type decimal = Expression.Evaluate( "type number", [ type number = type number] ),
type time = Expression.Evaluate( "type time", [ type time = type time] ),
type date = Expression.Evaluate( "type date", [ type date = type date] ),
type datetime = Expression.Evaluate( "type datetime", [ type datetime = type datetime] ),
type datetimezone = Expression.Evaluate( "type datetimezone", [ type datetimezone = type datetimezone] ),
View Text.SplitByPattern.pq
// splits text where every character before the last character is a number
// matching string will be the first item of the new text, but that can be modified by filling up instead
let
Source = "Appl2e Pe3ar Banan2a Toma4to Potato",
ToList = Text.Split(Source, " "),
#"Converted to Table" = Table.FromColumns({ToList}),
PatternCheck = Table.AddColumn(#"Converted to Table",
"Pattern",
each if List.Contains({"0".."9"}, Text.At([Column1], Text.Length([Column1])-2))
then [Column1]