Forum Discussion
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. Functions which marked red skips when i try load data into model.
request 1 - SessionId
request 2 - StatisticsSessionId
request 3 - NavigationFiltration
request 4 - AddStatisicsRequest
request 5 - StartBuild
request 6 - GetStatistics
As I understand PQ logics, result of requests should be used somewhere - then PQ will not skip steps. Can somebody explain me how can i write expression with if then else statement ( add an argument to the function from the result of the previous step that will perform an insignificant check), for example if prevstepcolumn <>null then
My query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
//request 1 - SessionId
SessionId = Table.AddColumn(Source, "Authorization", each fnAuthorization()),
#"Removed Columns" = Table.RemoveColumns(SessionId,{"Column1"}),
#"Expanded Authorization" = Table.ExpandTableColumn(#"Removed Columns", "Authorization", {"SessionId"}, {"SessionId"}),
//request 2 - StatisticsSessionId
StatisticsSessionId = Table.AddColumn(#"Expanded Authorization", "StartStatisticsSession", each fnStartStatisticsSession([SessionId])),
#"Expanded StartStatisticsSession" = Table.ExpandTableColumn(StatisticsSessionId, "StartStatisticsSession", {"StatisticsSessionId"}, {"StatisticsSessionId"}),
//request 3 - NavigationFiltration
NavigationFiltration = Table.AddColumn(#"Expanded StartStatisticsSession", "NavigationFiltration", each fnNavigationFiltration([SessionId], [StatisticsSessionId])),
//request 4 - AddStatisicsRequest
AddStatisicsRequest = Table.AddColumn(NavigationFiltration, "AddStatisicsRequest", each fnTrackPeriodsMileage_AddStatisicsRequest([SessionId], [StatisticsSessionId])),
//request 5 - StartBuild
StartBuild = Table.AddColumn(AddStatisicsRequest, "StartBuild", each fnStartBuild([SessionId], [StatisticsSessionId])),
//request 6 - GetStatistics
GetStatistics = Table.AddColumn(StartBuild, "GetStatistics", each Function.InvokeAfter(()=>fnTrackPeriodsMileage_GetStatistics([SessionId], [StatisticsSessionId]), #duration(0,0,0,15))),
Result = GetStatistics{0}[GetStatistics]
in
Result
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.
4 Replies
Update data in PQ
Update data in PowerBI
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
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.
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 SourceAnd 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.
