Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Allows indexing to be folded in a navigation table
section Section1;
// Does a short search on Wikipedia. This is meant to stand in for a function that returns the navigation data.
GetWikipediaSearch = (term) => Json.Document(Web.Contents("", [Query=[
gsrsearch="'" & term & "'"]]));
// Returns the data for a page on Wikipedia. This is meant to stand in for a function which returns e.g. specific table contents.
GetWikipediaPage = (pageid) as text => Text.FromBinary(Web.Contents("", [Query=[curid=Text.From(pageid)]]));
// This is a function which takes a search term and produces a navigation table consisting of matching hits in
// Wikipedia. Note that to get the full lazy behavior, you'll likely need to test in Power BI Desktop as the "data"
// in this case is text and the SDKs will tend to get those values too eagerly.
WikipediaSearchTable = (term) =>
pages = Record.ToTable(GetWikipediaSearch(term)[query][pages]),
expanded = Table.ExpandRecordColumn(pages, "Value", {"title"}),
renamed = Table.RenameColumns(expanded, {{"Name", "PageId"}, {"title", "Title"}}),
AsNavigationView = Table.NavigationTableView(() => renamed, {"PageId"}, GetWikipediaPage, [
Name = each [Title],
ItemKind = each "Value",
ItemName = each "Value",
IsLeaf = each true
// This is intended to be a reusable helper which takes a constructor for the base navigation table,
// a list of key columns whose values uniquely describe a row in the navigation table, a constructor
// for the table to returned as data for a given row in the navigation table, and a record with a
// description of how to construct the output navigation table.
// The baseTable constructor will only be invoked if necessary, such as when initially returning the
// navigation table. If a user query is something like "navTable{[Key1=Value1, Key2=Value2]}[Data]",
// then the code will not call the baseTable function and instead just call dataCtor(Value1, Value2).
// Obviously, dataCtor itself could return another navigation table.
// Disclaimer: this hasn't been as extensively tested as I'd like -- and in fact, I found and fixed a
// bug while setting up the test case above.
Table.NavigationTableView =
baseTable as function,
keyColumns as list,
dataCtor as function,
descriptor as record
) as table =>
transformDescriptor = (key, value) =>
map = [
Name = "NavigationTable.NameColumn",
Data = "NavigationTable.DataColumn",
Tags = "NavigationTable.TagsColumn",
ItemKind = "NavigationTable.ItemKindColumn",
ItemName = "Preview.DelayColumn",
IsLeaf = "NavigationTable.IsLeafColumn"
if value is list
then [Name=value{0}, Ctor=value{1}, MetadataName = Record.FieldOrDefault(map, key)]
else [Name=key, Ctor=value, MetadataName = Record.FieldOrDefault(map, key)],
fields = List.Combine({
List.Transform(keyColumns, (key) => [Name=key, Ctor=(row) => Record.Field(row, key), MetadataName=null]),
if Record.HasFields(descriptor, {"Data"}) then {}
else {transformDescriptor("Data", (row) => Function.Invoke(dataCtor, Record.ToList(Record.SelectFields(row, keyColumns))))},
Table.TransformRows(Record.ToTable(descriptor), each transformDescriptor([Name], [Value]))
metadata = List.Accumulate(fields, [], (m, d) => let n = d[MetadataName] in if n = null then m else Record.AddField(m, n, d[Name])),
tableKeys = List.Transform(fields, each [Name]),
tableValues = List.Transform(fields, each [Ctor]),
tableType = Type.ReplaceTableKeys(
Value.Type(#table(tableKeys, {})),
{[Columns=keyColumns, Primary=true]}
) meta metadata,
reduceAnd = (ast) => if ast[Kind] = "Binary" and ast[Operator] = "And" then List.Combine({@reduceAnd(ast[Left]), @reduceAnd(ast[Right])}) else {ast},
matchFieldAccess = (ast) => if ast[Kind] = "FieldAccess" and ast[Expression] = RowExpression.Row then ast[MemberName] else ...,
matchConstant = (ast) => if ast[Kind] = "Constant" then ast[Value] else ...,
matchIndex = (ast) => if ast[Kind] = "Binary" and ast[Operator] = "Equals"
if ast[Left][Kind] = "FieldAccess"
then Record.AddField([], matchFieldAccess(ast[Left]), matchConstant(ast[Right]))
else Record.AddField([], matchFieldAccess(ast[Right]), matchConstant(ast[Left]))
else ...,
lazyRecord = (recordCtor, keys, baseRecord) =>
let record = recordCtor() in List.Accumulate(keys, [], (r, f) => Record.AddField(r, f, () => (Record.FieldOrDefault(baseRecord, f, null) ?? Record.Field(record, f)), true)),
getIndex = (selector, keys) => Record.SelectFields(Record.Combine(List.Transform(reduceAnd(RowExpression.From(selector)), matchIndex)), keys)
Table.View(null, [
GetType = () => tableType,
GetRows = () => #table(tableType, List.Transform(Table.ToRecords(baseTable()), (row) => List.Transform(tableValues, (ctor) => ctor(row)))),
OnSelectRows = (selector) =>
index = try getIndex(selector, keyColumns) otherwise [],
default = Table.SelectRows(GetRows(), selector)
if Record.FieldCount(index) <> List.Count(keyColumns) then default
else Table.FromRecords({
index & lazyRecord(
() => Table.First(default),
List.Skip(tableKeys, Record.FieldCount(index)),
Record.AddField([], "Data", () => Function.Invoke(dataCtor, Record.ToList(index)), true))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment