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.

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

You can use if...then...else and/or try...otherwise at the table/step level too.  For example,

 

let

Source = ...

Step1 = Some.Function(Source, ...)

Step2 = if Table.RowCount(Step1)>1 then Some.Function(Step1, ...) else Some.OtherFunction(Source, ...)

in

Step2

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat