Forum Discussion

Bessonnizza's avatar
Bessonnizza
Helper II
6 years ago
Solved

PQ skips steps when loading data into the model

Hello there,   When i loading data into a model, PQ skips some steps, which shouldn't be skipped if i what to receive data in last step (API rules). In query i invoke coherently six function. Funct...
Need Help
  • SteveCampbell's avatar
    SteveCampbell
    6 years ago

    You can't really. Power Query is not designed to be a functional programming language, more a querying language. Therefore emphasis will be on optimising your query as much as possible instead of running each step.

     

    I believe you can use the function Table.Buffer . This will load the table into memory.

     

     

    E.G:

     

    //request 2 - StatisticsSessionId
    StatisticsSessionId = Table.AddColumn(#"Expanded Authorization", "StartStatisticsSession", each fnStartStatisticsSession([SessionId])),
    #"Expanded StartStatisticsSession" = Table.ExpandTableColumn(StatisticsSessionId, "StartStatisticsSession", {"StatisticsSessionId"}, {"StatisticsSessionId"}),

     

    //buff 2
    BUFFStatisticsSessionId = Table.Buffer( #"Expanded StartStatisticsSession"),

    //request 3 - NavigationFiltration
    NavigationFiltration = Table.AddColumn(BUFFStatisticsSessionId , "NavigationFiltration", each fnNavigationFiltration([SessionId], [StatisticsSessionId])),

     

     

     

    and so on. Although this actually loads each step into RAM so is not very efficient.

SteveCampbell's avatar
SteveCampbell
Memorable Member
6 years ago

You can't really. Power Query is not designed to be a functional programming language, more a querying language. Therefore emphasis will be on optimising your query as much as possible instead of running each step.

 

I believe you can use the function Table.Buffer . This will load the table into memory.

 

 

E.G:

 

//request 2 - StatisticsSessionId
StatisticsSessionId = Table.AddColumn(#"Expanded Authorization", "StartStatisticsSession", each fnStartStatisticsSession([SessionId])),
#"Expanded StartStatisticsSession" = Table.ExpandTableColumn(StatisticsSessionId, "StartStatisticsSession", {"StatisticsSessionId"}, {"StatisticsSessionId"}),

 

//buff 2
BUFFStatisticsSessionId = Table.Buffer( #"Expanded StartStatisticsSession"),

//request 3 - NavigationFiltration
NavigationFiltration = Table.AddColumn(BUFFStatisticsSessionId , "NavigationFiltration", each fnNavigationFiltration([SessionId], [StatisticsSessionId])),

 

 

 

and so on. Although this actually loads each step into RAM so is not very efficient.

  • Bessonnizza's avatar
    Bessonnizza
    Helper II
    6 years ago

    Ty for answer, Steve!

     

    I solved the problem in the following way: add additional verification (parameters) to all requests, for example check as text, check_result = check

    let
        Source = (AuthorizationId as text, StatisticsId as text, Check as text) => let
        data = Json.FromValue([StatisticsSessionId = StatisticsId]),
        headers = [#"accept"="*/*", #"ScoutAuthorization"=AuthorizationId, #"Content-Type"="application/json"],
        web = Web.Contents("AddStatisticsRequestURL", [ Content = data, Headers = headers, Timeout=#duration(0,0,0,10), ManualStatusHandling = {404, 400}]),
        result = Json.Document(web),
        check_result = Check
    in
        result
in
    Source

     

    And then when i invoke functions and used check this parameter by writing from the previous step

    //request 4 - AddStatisicsRequest
    AddStatisicsRequest = Table.AddColumn(#"Expanded NavigationFiltration.Status", "AddStatisicsRequest", each fnTrackPeriodsMileage_AddStatisicsRequest([SessionId], [StatisticsSessionId], [NavigationFiltration.Status.Value])),

     

    I think the way with a buffer will work too.