Hi All,
I have the following power query which I use to load data from an api call. This query works but the challenge is it takes too much time to load, I have 10K records (rows) which takes 30-40 mins to load.
One another challenge with json output (of the api call) is that the columns I need for my analysis, I buried deep down in a nested list/record. So I have to expand the json output into a columnar structure with every key being a column name (hence you see the following functions).
I have been reading about making queries faster, I have come across the function `table.buffer` but still confused as to where to use it. So I put it whereever I can.
I will really appreciate any help regarding making this code faster or in general tips to make the code faster.
source = Json.Document(contents),
issues = source[issues],
tble = Table.FromRecords(issues),
//expand list column function
expandListColumn = (Table, ColumnName) => Table.ExpandListColumn(
Table, ColumnName
),
//expand record column
expandRecordColumn = (Table, ColumnName) => Table.ExpandRecordColumn(
Table,
ColumnName,
Record.FieldNames(
List.Select(
Table.Column(Table, ColumnName),
each Type.Is(Value.Type(_), type record)
){0}),
List.Accumulate(
Record.FieldNames(
List.Select(
Table.Column(Table, ColumnName),
each Type.Is(Value.Type(_), type record)
){0}),
{},
(s, c) => List.Combine({s, {Text.Combine({ColumnName, ".", c})}})
)
),
//check for if the column is of type list or record
listOrRecordColumnNames = (Table) => List.Accumulate(
Table.ColumnNames(Table),
{},
(s, c) => s & (
if List.MatchesAny(
Table.Column(Table, c),
each Type.Is(Value.Type(_), type record) or Type.Is(Value.Type(_), type list)
)
then {c}
else {}
)
),
//recursively expand columns
expandColumn = (Table) => List.Accumulate(
listOrRecordColumnNames(Table),
Table,
(state, current) => if List.MatchesAny(
Table.Column(Table, current),
each Type.Is(Value.Type(_), type record)
) then expandRecordColumn(state, current)
else expandListColumn(state, current)
),
expandColumnsRecursive = (Table) => (
if List.IsEmpty(
listOrRecordColumnNames(Table)
)
then Table
else
let
tempTable = expandColumn(Table),
tempTable2 = Table.Buffer(tempTable),
NextTable = @expandColumnsRecursive(tempTable2)
in
NextTable
),
expandedTable = expandColumnsRecursive(tble),source = Json.Document(contents),
issues = source[issues],
tble = Table.FromRecords(issues),
//expand list column function
expandListColumn = (Table, ColumnName) => Table.ExpandListColumn(
Table, ColumnName
),
//expand record column
expandRecordColumn = (Table, ColumnName) => Table.ExpandRecordColumn(
Table,
ColumnName,
Record.FieldNames(
List.Select(
Table.Column(Table, ColumnName),
each Type.Is(Value.Type(_), type record)
){0}),
List.Accumulate(
Record.FieldNames(
List.Select(
Table.Column(Table, ColumnName),
each Type.Is(Value.Type(_), type record)
){0}),
{},
(s, c) => List.Combine({s, {Text.Combine({ColumnName, ".", c})}})
)
),
//check for if the column is of type list or record
listOrRecordColumnNames = (Table) => List.Accumulate(
Table.ColumnNames(Table),
{},
(s, c) => s & (
if List.MatchesAny(
Table.Column(Table, c),
each Type.Is(Value.Type(_), type record) or Type.Is(Value.Type(_), type list)
)
then {c}
else {}
)
),
//recursively expand columns
expandColumn = (Table) => List.Accumulate(
listOrRecordColumnNames(Table),
Table,
(state, current) => if List.MatchesAny(
Table.Column(Table, current),
each Type.Is(Value.Type(_), type record)
) then expandRecordColumn(state, current)
else expandListColumn(state, current)
),
expandColumnsRecursive = (Table) => (
if List.IsEmpty(
listOrRecordColumnNames(Table)
)
then Table
else
let
tempTable = expandColumn(Table),
tempTable2 = Table.Buffer(tempTable),
NextTable = @expandColumnsRecursive(tempTable2)
in
NextTable
),
expandedTable = expandColumnsRecursive(tble),
