New Column returns a list, when I try to access list using {0} it refreshed forever

[jburbano]

This is my code:

let
    Source = AzureStorage.Tables("mytable"),
    Environments1 = Source{[Name="Environments"]}[Data],
    #"Expanded Content" = Table.ExpandRecordColumn(Environments1, "Content", {"environmentId", "environment", "releaseId", "envName", "envTTD", "preApprover", "postApprover", "startedOn", "branchNames"}, {"environmentId", "environment", "releaseId", "envName", "envTTD", "preApprover", "postApprover", "startedOn", "branchNames"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Content","","{}",Replacer.ReplaceValue,{"environment"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Replaced Value", "environment", "environmentString"),
    #"Parsed JSON" = Table.TransformColumns(#"Duplicated Column",{{"environment", Json.Document}}),
    #"Expanded environment" = Table.ExpandRecordColumn(#"Parsed JSON", "environment", {"id", "releaseId", "name", "status", "variables", "preDeployApprovals", "postDeployApprovals", "preApprovalsSnapshot", "postApprovalsSnapshot", "deploySteps", "rank", "definitionEnvironmentId", "environmentOptions", "demands", "conditions", "createdOn", "modifiedOn", "workflowTasks", "deployPhasesSnapshot", "owner", "schedules", "release", "releaseDefinition", "releaseCreatedBy", "triggerReason", "timeToDeploy", "processParameters", "preDeploymentGatesSnapshot", "postDeploymentGatesSnapshot", "scheduledDeploymentTime"}, {"environment.id", "environment.releaseId", "environment.name", "environment.status", "environment.variables", "environment.preDeployApprovals", "environment.postDeployApprovals", "environment.preApprovalsSnapshot", "environment.postApprovalsSnapshot", "environment.deploySteps", "environment.rank", "environment.definitionEnvironmentId", "environment.environmentOptions", "environment.demands", "environment.conditions", "environment.createdOn", "environment.modifiedOn", "environment.workflowTasks", "environment.deployPhasesSnapshot", "environment.owner", "environment.schedules", "environment.release", "environment.releaseDefinition", "environment.releaseCreatedBy", "environment.triggerReason", "environment.timeToDeploy", "environment.processParameters", "environment.preDeploymentGatesSnapshot", "environment.postDeploymentGatesSnapshot", "environment.scheduledDeploymentTime"}),
    #"Expanded environment.releaseDefinition" = Table.ExpandRecordColumn(#"Expanded environment", "environment.releaseDefinition", {"id", "name", "path", "projectReference", "url", "_links"}, {"environment.releaseDefinition.id", "environment.releaseDefinition.name", "environment.releaseDefinition.path", "environment.releaseDefinition.projectReference", "environment.releaseDefinition.url", "environment.releaseDefinition._links"}),
    #"Expanded environment.conditions" = Table.ExpandListColumn(#"Expanded environment.releaseDefinition", "environment.conditions"),
    #"Expanded environment.conditions1" = Table.ExpandRecordColumn(#"Expanded environment.conditions", "environment.conditions", {"result", "name", "conditionType", "value"}, {"environment.conditions.result", "environment.conditions.name", "environment.conditions.conditionType", "environment.conditions.value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded environment.conditions1",{{"environment.createdOn", type datetime}, {"environment.modifiedOn", type datetime},{"environment.rank", Int64.Type},{"environment.timeToDeploy", type number},{"envTTD", type number},{"releaseId", Int32.Type}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type", "environment.createdOn", "CreatedOn"),
    #"Extracted Date" = Table.TransformColumns(#"Duplicated Column1",{{"CreatedOn", DateTime.Date, type date}}),
    #"Inserted Parsed Date" = Table.AddColumn(#"Extracted Date", "environment.startedOn", each Date.From(DateTimeZone.From([startedOn])), type datetime),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Parsed Date",{{"startedOn", type datetime}, {"environment.startedOn", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"startedOn", Order.Ascending}}),
    #"Added Platform" = Table.AddColumn(
        #"Sorted Rows", 
        "Platform", 
        each (
            if (
                List.Contains(
                    Table.ToList(
                        Prefixes
                    ),                 
                    Text.Split(
                        [environment.releaseDefinition.name], 
                        "-"
                    ){0}
                )
            ) then (
                Text.Split(
                    [environment.releaseDefinition.name], 
                    "-"
                ){0}
            ) else (
                "N/A"
            )
		),
		type text
	),
    #"Added Predecessor" = Table.AddColumn(
        #"Added Platform", 
        "Predecessor", 
        each (
			let #"Predecessor Parent" = _ in
            let 
				#"Predecessor Source" = Table.SelectRows(
					#"Added Platform",
					each (
						[PartitionKey] = #"Predecessor Parent"[PartitionKey] and [releaseId] = #"Predecessor Parent"[releaseId] and [environment.rank] = #"Predecessor Parent"[environment.rank]-1
					)
				)[environment.status]{0}
			in				
			#"Predecessor Source"
		),
		type text
	)
in
    #"Added Predecessor"

The new column never finsihes refreshing.  If I remove the {0}, it finally does load, but as a list type.  Not sure what I am doing wrong. I am also trying different iterations of this, like putting the [environment.status]{0} at #"Predecessor Source" and also using List.First().

[PwerQueryKees]

You say "Never Finishes", but how long did you wait? And how many rows does you table have?
If the table is very large and you do a 'SelectRows' of the whole table for every row in the table, it could be a performance issue.
You don't necessarily see that if you let powerquery return the list, because it does not have to perform the actual selectrows to know it is returning a list. I may delay evaluation of the SelectRows until you actually need the content of the list...

[Anonymous · Accepted solution]

Hi @jburbano ,

Thanks for watkinnc's reply!

And @jburbano , The issue you're experiencing with the column not finishing refreshing when you use `{0}` in your Power Query code is likely due to the fact that when `{0}` is used, it attempts to access the first element of a list. If the list is empty (i.e., no rows match the filter criteria in `Table.SelectRows`), this will lead to an error or cause the operation to hang as it cannot return a value.
Please try to adjust your code like this and see whether it will work:

#"Added Predecessor" = Table.AddColumn(
    #"Added Platform", 
    "Predecessor", 
    each (
        let #"Predecessor Parent" = _ in
        let 
            #"Predecessor Source" = Table.SelectRows(
                #"Added Platform",
                each (
                    [PartitionKey] = #"Predecessor Parent"[PartitionKey] and 
                    [releaseId] = #"Predecessor Parent"[releaseId] and 
                    [environment.rank] = #"Predecessor Parent"[environment.rank]-1
                )
            )
        in
        if Table.IsEmpty(#"Predecessor Source") then 
            "N/A" 
        else 
            List.First(#"Predecessor Source"[environment.status], "N/A")
    ),
    type text
)

If this method doesn't work, then as @SU said, it's difficult for us to help you find a solution with only the M code and no other information. Please provide as much information as possible. Thank you!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise]

It is hard to follow the code like this, can you provide at least two row table the sample of your data?

[Anonymous]

Maybe also try [List]{0}{0}

--Nate

[Anonymous]

Have you "stepped through" any of this to see if these list results are returning what they think you are returning? I think you are trying to split lists of lists somewhere.

--Nate