Skip to content

Instantly share code, notes, and snippets.

Avatar

Imke Feldmann ImkeF

View GitHub Profile
@ImkeF
ImkeF / Table.RemoveSPListColumns.pq
Last active Oct 16, 2020
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", 
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 Nov 19, 2019
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 Apr 18, 2021
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 wholenumber = Expression.Evaluate( "Int64.Type", [ #"Int64.Type" = Int64.Type] ),
type currency = Expression.Evaluate( "Currency.Type", [ #"Currency.Type" = Currency.Type] ),
type percentage = Expression.Evaluate( "Percentage.Type", [ #"Percentage.Type" = Percentage.Type] ),
type time = Expression.Evaluate( "type time", [ type time = type time] ),
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]
@ImkeF
ImkeF / PowerBI.DataModel.pq
Last active May 21, 2020
Returns port number and database name of the latest Power BI data model opened with PBI Desktop.
View PowerBI.DataModel.pq
let func =
(optional Model as text) =>
let
Source_Port = Folder.Files("C:\Users"),
msmdsrv = Table.SelectRows(Source_Port, each [Name] = "msmdsrv.port.txt"),
#"Sorted Rows" = Table.Buffer(Table.Sort(msmdsrv,{{"Date created", Order.Descending}})),
Select_Last_Instance_Active = Table.FirstN(#"Sorted Rows",1),
#"Combined Binaries" = Binary.Combine(Select_Last_Instance_Active[Content]),
#"Imported Text" = Table.FromColumns({Lines.FromBinary(#"Combined Binaries",null,null,1252)}),
@ImkeF
ImkeF / Text.RemoveBetweenDelimiters.pq
Last active Oct 11, 2020
Removes text between 2 delimiters.
View Text.RemoveBetweenDelimiters.pq
let func =
(TextToClean as text, StartDelimiter as text, EndDelimiter as text, optional RemoveDelimiters) =>
let
removeDelimiters = if RemoveDelimiters = null then StartDelimiter & EndDelimiter else "",
Source = Text.From(TextToClean),
FirstSplit = List.Buffer( Text.Split(Source, StartDelimiter) ),
Custom2 = if List.First(FirstSplit) = "" then List.Skip(FirstSplit) else FirstSplit,
Custom1 = List.Transform(Custom2, each if Text.Contains(_, EndDelimiter) then Text.AfterDelimiter(_, EndDelimiter, 0) else _),
ListSelect = List.Select(Custom1, each _<>""),
TextCombine = Text.Combine(ListSelect, removeDelimiters)
@ImkeF
ImkeF / DAX.VariableDebugger.pq
Last active Nov 18, 2019
Produces DAX code to debug the variables in a DAX measure.
View DAX.VariableDebugger.pq
let func =
let
Source = (columnNames as text, optional topN as number, optional measureCode as text) =>
let
/* Debug parameters
measureCode = DAXMeasureCode,
columnNames = "RankItem,RankMeasure, null, Rank, TopOrOthers, TopN_Others",
topN = 5,