CODEWITHSUNDEEP
powerquerym
Jack_Johnson
Jack_Johnson

Reputation: 23

Why is 'RecursiveExpand' not recognised in this Power Query

I've got this Power Query code, that I feel should be working, however it keeps popping up with "Expression.Error: The name 'RecursiveExpand' wasn't recognized. Make sure it's spelled correctly."

Code is calling an API to get data from JIRA, then to expand all record type columns dynamically.

let
    Source = 100,
    JiraIDPerPage = 100,
    GetJson = (maxR, startA) =>
        let 
            Json = Json.Document(
                Web.Contents(
                    #"URI_BASE (2)",
                    [
                    RelativePath = #"URI_PATH (2)" & #"URI_RESOURCE (2)",
                    Content = Json.FromValue(
                        [
                            jql = "project in (" & #"JIRA_PROJECT_KEYS (2)" & ")",
                            fields = {"*all"},
                            maxResults = maxR,
                            startAt = startA
                        ]
                    ),
                    Headers =
                        [
                        Authorization = "Basic " & Binary.ToText(Text.ToBinary(#"USERNAME_TOKEN (2)"), 0),
                        Accept = "application/json",
                        #"Content-Type"="application/json"
                        ]
                    ]
                )
            )
        in  Json,
    GetJiraIDCount = () =>
        let maxResultss  = 0,
            startAtt = 0,
            Json  = GetJson(maxResultss, startAtt),
            Count = Json[#"total"]
        in  Count,
    GetPage = (Index) =>
        let Top   = 100,
            Skip  = Index * 100,
            Json  = GetJson(Top, Skip),
            Value = Json[#"issues"]
        in  Value,
    JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
    PageCount = Number.RoundUp(JiraIDCount / JiraIDPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages = List.Transform(PageIndices, each GetPage(_)),
    JiraID = List.Union(Pages),
    Table = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"expand", "id", "self", "key", "fields"}, {"expand", "id", "self", "key", "fields"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"expand", "id", "self"}),
    Custom1 = let
    // Function to recursively expand a table
    RecursiveExpand = (table as table) =>
    let
        // Get a list of columns that are lists
        listColumns = Table.ColumnsOfType(table, {type list}),
        // Use List.Accumulate to expand each list column
        expandedTable = List.Accumulate(listColumns, table, (state, current) => Table.ExpandListColumn(state, current[Name])),
        // Check if there are still nested lists
        nestedListsExist = Table.ColumnsOfType(expandedTable, {type list}),
        // If nested lists still exist, call the function recursively
        result = if List.IsEmpty(nestedListsExist) then expandedTable else RecursiveExpand(expandedTable)
    in
        result,
    // Start the recursive expansion
    expandedTable = RecursiveExpand(#"Removed Columns")
in
    expandedTable
in
    Custom1

I've searched for an answer to find why it's not recognised but I'm not finding any answers.

Upvotes: 2

Views: 115

Answers (1)

SSlinky
SSlinky

Reputation: 488

You need an @ to refer to your recursive call in code.

result = if List.IsEmpty(nestedListsExist) then expandedTable else @RecursiveExpand(expandedTable)

Upvotes: 1

Related Questions