Skip to content

Instantly share code, notes, and snippets.

Avatar

Imke Feldmann ImkeF

View GitHub Profile
View GetCodeFromCsv.pq
let
// Fetches your function library from a csv-file: 3 columns "Column1" is autogenerated and will be removed, "GetCode" hold code and "Name" the name of the function
// Adjust "Path" to your own machine:
Path = "C:\Users\imkef\Desktop\ImkesFunctions.csv",
// The Encoding parameters might need to be adjusted to local settings
Source = Csv.Document(File.Contents(Path),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Column1"}),
EvaluateFunction = Table.AddColumn(#"Removed Columns", "Value", each Expression.Evaluate([GetCode], #shared)),
@ImkeF
ImkeF / R Trend.pq
Last active Sep 19, 2017
M functions
View R Trend.pq
let func =
// fnStat.Trend
// Author: Imke Feldmann - http://www.thebiccountant.com/ - Link to blogpost: http://wp.me/p6lgsG-Fd
(Actuals as table, FCPeriods as list) =>
let
FCPeriods = Table.FromList(FCPeriods, Splitter.SplitByNothing()),
#"Run R Script" = R.Execute("y <- as.matrix(Actuals[1])
x <- as.matrix(Actuals[2])
trendline <- fitted(lm(y ~ x))
View Text.TrimAll.pq
// Removes all duplicate separators within a string. If optional "TrimEnds" is set to "yes" the (single remaining) separators will be removed from the start and end as well.
(MyText as text, Separator as text, optional TrimEnds as text) as text =>
let
TransformTextToList = List.Buffer(Text.ToList(MyText)),
Result = List.Accumulate(TransformTextToList, // List that will be iterated through
"", // a starting value (if necessary, here we leave it blank)
(resultSoFar, current) => if Text.End(resultSoFar,1) = Separator and current = Separator then resultSoFar else resultSoFar&current // operation that will be performed at each iteration-step
),
TrimEnd = if TrimEnds = "yes" then Text.TrimStart(Text.TrimEnd(Result, Separator), Separator) else Result
View Table.SolveParentChild.pq
let func =
(ParChTable as table,
ChildKey as text,
ParentKey as text,
LevelColumnName as text) =>
let
/*//Debug Parameters
ParChTable = Nodes0,
View Type.Text.pq
let func =
(Value as any) as text =>
let
t = Value.Type(Value),
nonNullableType = Type.NonNullable(t),
TypeDescription =
if Type.Is(nonNullableType, type number) then "number"
@ImkeF
ImkeF / power-query-iso-week-year.md
Created Mar 9, 2018 — forked from r-k-b/power-query-iso-week-year.md
M functions to convert between ISO 8601 Week & Year ⇄ dates (e.g., `2014-12-29` ⇄ `"2015-W01-1"`)
View power-query-iso-week-year.md
View Text.QueryNames.pq
let func =
(optional SelectedType as type) =>
let
/* Debug Parameters
SelectedType = type table,
End of debug parameters */
Source = #shared,
ToTable = Record.ToTable(Source),
View Table.ExpandAllColumns.pq
let func =
(TableColumn as list, optional SourceNameColumn as list) =>
let
AddIDs =
if
SourceNameColumn=null
then
TableColumn
else
let
View Date.DatesBetween.pq
let
// ----------------------- Documentation -----------------------
documentation_ = [
Documentation.Name = " Dates.ListDateIntervals
", Documentation.Description = " Creates a list of dates according to the chosen interval between Start and End. Allowed values for 3rd parameter: ""Year"", ""Quarter"", ""Month"", ""Week"" or ""Day"".
" , Documentation.LongDescription = " Creates a list of dates according to the chosen interval between Start and End. The dates created will always be at the end of the interval, so could be in the future if today is chosen.
", Documentation.Category = " Table
", Documentation.Source = " http://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/ .
", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com .
", Documentation.Examples = {[Description = " see http://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/ .
View Record.AllQueries.pq
let func =
(optional SelectedType as type, optional ExcludeList as list) =>
let
/* Debug Parameters
SelectedType = type table,
ExcludeList = null,
End of debug parameters */
//By default, the query "GetAllQueries" will be excluded, so if you name this query so, you get everything BUT this query.