cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Refactoring power query to reduce load time

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),

 

 

2 REPLIES 2
Super User III
Super User III

1. If your API data source is slow to return the records then Table.Buffer is of no help

2. Can you limit the API response to only the fields you need?

3. You can shortcut the JSON traversal with the [key1][key2][key3][value]  syntax, like a JSON parser does.

Is there a way to do profiling of query steps i.e. to find which step is taking more time to execute? As for api call, when I try the same api call via postman or curl, I get the result immediately. So my guess is some functions down the line are taking too much time to execute.

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors