Skip to content

Instantly share code, notes, and snippets.

Curt Hagenlocher CurtHagenlocher

View GitHub Profile
@CurtHagenlocher
CurtHagenlocher / AzureTable.pq
Created Aug 13, 2019
Work-in-progress Azure Table Storage
View AzureTable.pq
section AzStorage;
[DataSource.Kind="TableStorage"]
shared TableStorage.Contents = (url) =>
Text.FromBinary(Web.Contents(url, [Headers=SignRequest(url)]));
[DataSource.Kind="TableStorage"]
shared FileStorage.Contents = (url) =>
Text.FromBinary(Web.Contents(url, [Headers=SignRequestBlob(url)]));
@CurtHagenlocher
CurtHagenlocher / FoldedRowExpression.pq
Last active Mar 29, 2019
Given a table type and a function, returns the AST for the foldable function as JSON
View FoldedRowExpression.pq
(tableType as type, fn as function) =>
let
TransformNode = (x) => Record.FromList(List.Transform(Record.FieldValues(x), TransformValue), Record.FieldNames(x)),
TransformValue = (x) => if x is record then TransformRecord(x) else if x is list then TransformList(x) else x,
TransformList = (x) => List.Transform(x, TransformValue),
TransformRecord = (x) =>
if x = RowExpression.Row then [Kind="Row"]
else if x[Kind] = "Constant" then TransformConstant(x)
else TransformNode(x),
TransformConstant = (x) => if x[Value] is function and TryFunctionName(x[Value]) <> null then [Kind="Identifier", Name=TryFunctionName(x[Value])]
View DetectLanguage.pq
section Section1;
Detect1000Languages = (input as list) as list =>
let
// TODO: support nulls, truncate text to avoid service limits
text = List.Buffer(input),
data = Table.FromColumns({text}, type table [text=text]),
indexed = Table.AddIndexColumn(data, "id", 1),
textId = Table.TransformColumnTypes(indexed, {{"id", type text}}),
body = [documents=Table.ReorderColumns(textId, {"id", "text"})],
View RowExpression.From.pq
let
Value.FixType = (value, optional depth) =>
let
nextDepth = if depth = null then 3 else depth - 1,
result = if depth = 0 then null
else if value is type then TextType(value)
else if value is table then Table.TransformColumns(value, {}, @Value.FixType)
else if value is list then List.Transform(value, each @Value.FixType(_, nextDepth))
else if value is record then
Record.FromList(List.Transform(Record.ToList(value), each @Value.FixType(_, nextDepth)), Record.FieldNames(value))
@CurtHagenlocher
CurtHagenlocher / multipart.m
Created Jul 20, 2015
Using multipart/form-data with Power Query. This assumes that any files being uploaded are textual; changing this to support arbitrary binary data is possible, but wasn't interesting to me.
View multipart.m
let
Web.MultiPartPost = (url as text, parts as record) as binary =>
let
unique = Text.NewGuid(),
boundary = "--" & unique,
crlf = "#(cr)#(lf)",
item = (name, value) =>
let
filename = Value.Metadata(value)[name]?,
contentType = Value.Metadata(value)[type]?,
@CurtHagenlocher
CurtHagenlocher / json.m
Created Jul 8, 2015
Format M data as JSON
View json.m
let
ByteToHex = (i as number) as text =>
let
chars = "0123456789abcdef",
low = Text.Range(chars, Number.Mod(i, 16), 1),
high = Text.Range(chars, Number.RoundDown(i / 16), 1)
in high & low,
Json.EscapeChar = (text as text) as text =>
if text = """" or text = "\" or text = "/" then "\" & text
else if Character.ToNumber(text) < 32 then "\u00" & ByteToHex(Character.ToNumber(text))
@CurtHagenlocher
CurtHagenlocher / EnforceSchema.m
Created Jul 8, 2015
Demonstrates some mechanisms in M for applying or enforcing a predefined schema onto a table.
View EnforceSchema.m
let
EnforceTypes = (table as table, schema as table) as table =>
let
map = (t) => if t = type list or t = type record or t = type any then null else t,
mapped = Table.TransformColumns(schema, {"Value", map}),
omitted = Table.SelectRows(mapped, each [Value] <> null),
transforms = Table.ToRows(omitted),
changedType = Table.TransformColumnTypes(table, transforms)
in
changedType,
@CurtHagenlocher
CurtHagenlocher / FilterByValues.m
Created May 14, 2015
Filter by values from another table
View FilterByValues.m
let
data = Table.FromColumns({{1, 2, 3, 4, 5}, {"Left", "Right", "Center", "Left", "Right"}}),
filter = Table.FromColumns({{"Right", "Center"}}),
filterValues = filter[Column1],
filtered = Table.SelectRows(data, each List.Contains(filterValues, [Column2]))
in
filtered
@CurtHagenlocher
CurtHagenlocher / Web.ContentsCustomRetry.m
Created Apr 30, 2015
Demonstrates the use of a custom retry duration with Web.Contents.
View Web.ContentsCustomRetry.m
let
Value.WaitFor = (producer as function, interval as function, optional count as number) as any =>
let
list = List.Generate(
() => {0, null},
(state) => state{0} <> null and (count = null or state{0} < count),
(state) => if state{1} <> null
then {null, state{1}}
else {1 + state{0}, Function.InvokeAfter(() => producer(state{0}), interval(state{0}))},
(state) => state{1})
View ReadTwitterArchive.m
let
Source = Text.FromBinary(File.Contents("D:\testdata\tweet.json")),
Eol = Text.PositionOf(Source, "#(lf)"),
Skipped = Text.Range(Source, Eol + 1),
Tweets = Json.Document(Skipped)
in
Tweets
You can’t perform that action at this time.