Skip to content

Instantly share code, notes, and snippets.

Avatar

Imke Feldmann ImkeF

View GitHub Profile
@ImkeF
ImkeF / ExportCsv.pq
Last active March 26, 2019 20:13
M functions
View ExportCsv.pq
// Exports the current table to csv. Pass full PathFile specification as parameter
(path as text, dataset as table) =>
let
ConvertedPath = Text.Replace(path, "\", "/"),
RScript = R.Execute("write.csv(dataset,"""&ConvertedPath&""")
output <- dataset
",[dataset=dataset]),
output = RScript{[Name="output"]}[Value]
in
output
@ImkeF
ImkeF / R Trend.pq
Last active September 19, 2017 18:27
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))
@ImkeF
ImkeF / Record.FunctionToTable.pq
Last active November 18, 2019 20:56
M functions
View Record.FunctionToTable.pq
let func =
(FunctionRecord as record) =>
let
Source = Record.ToTable(FunctionRecord),
Meta = Table.AddColumn(Source, "Meta", each Value.Metadata(Value.Type([Value]))),
ColNames1 = Record.FieldNames(Record.Combine(Meta[Meta])),
#"Expanded Meta" = Table.ExpandRecordColumn(Meta, "Meta", ColNames1),
Expand1 = Table.ExpandListColumn(#"Expanded Meta", "Documentation.Examples"),
ColNames2 = Record.FieldNames(Record.Combine(List.Select(Expand1[Documentation.Examples], each _<>null))),
@ImkeF
ImkeF / GetCodeFromGitHub.pq
Last active November 18, 2019 20:56
M functions
View GetCodeFromGitHub.pq
let func =
(MasterPath as text, BlobPath as text) =>
let
/* debug parameters
MasterPath = "https://github.com/ImkeF/RM/tree/master",
BlobPath = "https://github.com/ImkeF/RM/blob/master",
*/
FileEndings = {".rm", ".m"},
Source = Web.Page(Web.Contents(MasterPath)),
@ImkeF
ImkeF / Table.ToFunctionRecord.pq
Last active November 18, 2019 20:55
M functions
View Table.ToFunctionRecord.pq
let func =
(TableWithCode as table, CodeColumnName as text) =>
let
Source= TableWithCode,
EvaluateFunction = Table.AddColumn(Source, "Value", each Expression.Evaluate(Record.Field(_, CodeColumnName), #shared)),
Cleanup = Table.RemoveColumns(EvaluateFunction,{CodeColumnName}),
ToRecord = Record.FromTable(Cleanup)
in
ToRecord
@ImkeF
ImkeF / GetCodeFromCsv.pq
Last active September 19, 2017 18:25
M functions
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 / ComplexJsonRecord.pq
Last active November 19, 2019 11:44
TheBIccountant
View ComplexJsonRecord.pq
(URL as text) =>
let
MyJsonRecord = Json.Document(Web.Contents(URL)),
MyJsonTable= Table.FromRecords( { MyJsonRecord } )
in
MyJsonTable
View MQueriesPBIX.pq
(Filename as text) =>
let
// Unz-function from: https://querypower.com/2017/03/22/extracting-power-queries-in-m/
Unz = (binaryZip,fileName) =>
let
//shorthand
UInt32 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
UInt16 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
View MetaQueriesPBIX
(PQTable as table) =>
let
#"Added Index" = Table.AddIndexColumn(PQTable, "Index", 0, 1),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Column1", "Column1 - Copy"),
View ParentChildAllParents.pq
///*
let func =
(ParChTable as table,
ChildKey as text,
ParentKey as text,
LevelColumnName as text) =>
//*/
let