DataFlow Power Query optimisation

[scott_hsd]

I have a query setup in a DataFlow and it takes a fair amount of time. From what I can see most of it is in the Expansion steps, although there is a lot of data. I wanted to know is there any room for improvement on the below steps, can you combine expansions?

This is basically working through a paged JSON response and drilling down to what is called the daily data.

let
  // Concatenates the Consumer Key & Consumer Secret and converts to base64
  authKey = "Basic " & Binary.ToText(Text.ToBinary(#"CLIENT_ID" & ":" & #"CLIENT_SECRET"),0),
  url = TOKEN_URL,
  // Uses the POST oauth2/token method to obtain a bearer token
  GetJson = Web.Contents(url,
     [
         Headers = [#"Authorization"=authKey,
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
         Content = Text.ToBinary("grant_type=client_credentials") 
     ]
 ),
  FormatAsJson = Json.Document(GetJson),
  // Gets token from the Json response
  AccessToken = FormatAsJson[access_token],
  AccessTokenHeader = "bearer " & AccessToken,
  // Uses the GET search method using the bearer token from the previous POST oauth2/token method
  GetJsonQuery = Web.Contents("https://api.primetric.com/beta/assignments/",
     [
         Headers = [#"Authorization"=AccessTokenHeader]
     ]
 ),
  // Loop through all the pages
  Source = Json.Document(GetJsonQuery),
  //ToTable = Record.ToTable(Source),
  //RelativePath = Source[next],
  //#"Split text" = "?page=" & Text.AfterDelimiter(RelativePath, "="),
  Custom1 = List.Generate( () =>
        [ResultRecord = Source],
        each [ResultRecord] <> null,
        each [
            ResultRecord = try Json.Document(Web.Contents("https://api.primetric.com/beta/assignments/"
        //    ResultRecord = try Json.Document(Web.Contents([ResultRecord][next]
        // Commented out as this query wont save in a dataflow with a dynamic URL, you need to uncomment the below for this to work
            ,[
                    RelativePath = "?page=" & Text.AfterDelimiter([ResultRecord][next],"="),
                    Headers = [#"Authorization"=AccessTokenHeader]
             ]
             )) otherwise null
        ]
    ),
  #"Converted to table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to table", "Column1", {"ResultRecord"}, {"ResultRecord"}),
  #"Expanded ResultRecord" = Table.ExpandRecordColumn(#"Expanded Column1", "ResultRecord", {"results"}, {"results"}),
  #"Expanded results" = Table.ExpandListColumn(#"Expanded ResultRecord", "results"),
  #"Expanded results 2" = Table.ExpandRecordColumn(#"Expanded results", "results", {"uuid", "daily_data"}, {"uuid", "daily_data"}),
  #"Expanded daily_data 1" = Table.ExpandRecordColumn(#"Expanded results 2", "daily_data", {"start", "end", "tracked", "scheduled"}, {"start", "end", "tracked", "scheduled"}),
  #"Expanded tracked 1" = Table.ExpandListColumn(#"Expanded daily_data 1", "tracked"),
  #"Expanded scheduled 1" = Table.ExpandListColumn(#"Expanded tracked 1", "scheduled"),
  #"Changed column type" = Table.TransformColumnTypes(#"Expanded scheduled 1", {{"uuid", type text}, {"start", type date}, {"end", type date}, {"tracked", Int64.Type}, {"scheduled", Int64.Type}}),
  #"Added index" = Table.AddIndexColumn(#"Changed column type", "Index", 3000001, 1, Int64.Type)
in
  #"Added index"

[v-jingzhang · Accepted solution]

Hi @scott_hsd 

You can combine multiple steps into one step. For example, combine below three steps 

#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to table", "Column1", {"ResultRecord"}, {"ResultRecord"}),
#"Expanded ResultRecord" = Table.ExpandRecordColumn(#"Expanded Column1", "ResultRecord", {"results"}, {"results"}),
#"Expanded results" = Table.ExpandListColumn(#"Expanded ResultRecord", "results"),

into 

#"Expanded results" = Table.ExpandListColumn(Table.ExpandRecordColumn(Table.ExpandRecordColumn(#"Converted to table", "Column1", {"ResultRecord"}, {"ResultRecord"}), "ResultRecord", {"results"}, {"results"}), "results"),

Use the expression after "=" to replace the referenced table name in its next step. But I'm not sure if this would improve the performance. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[scott_hsd]

Thanks I did this and yes you are right, it did not improve the performance at all.

[v-jingzhang]

Hi @scott_hsd 

I saw below notice on Power BI Support page. Not sure if this is a possible cause of the slow query. 

Jing