Power Query M, рекурсивное создание плоского списка из многомерного списка

В Power Query M я пытаюсь создать рекурсивную функцию, которая превратит беспорядок многомерных списков и записей в один плоский список записей, чтобы можно было легко манипулировать записями в PowerBI.

Я работал с рекурсией на других языках, но я новичок в использовании M.

Беспорядок из списков и записей похож по структуре на это:

  • Event
    • Event Details
      • Payments
        • Payment Details

Есть небольшие отличия, но они не должны иметь значения.

Я надеюсь, что вывод будет похож на этот:

{
    [event1, eventDetail1, payment1, paymentDetails1],
    [event1, eventDetail1, payment1, paymentDetails2],
    [event1, eventDetail1, payment1, paymentDetails3],
    [event1, eventDetail1, payment2, paymentDetails1],
}

Продолжение для каждого отдельного элемента.

Это рекурсивная функция, которая у меня есть сейчас:

recursiveCollapse = (uncleanedList as list, eventCounter as number, paymentCounter as number, finalList as list) =>
        let
            eventLength = List.Count(uncleanedList),
            firstIf = if eventCounter < eventLength then
                let
                    secondIf = if paymentCounter < List.Count(uncleanedList{eventCounter}[eventPayments]) then
                        finalList = @recursiveCollapse(uncleanedList, eventCounter, paymentCounter + 1, finalList & {
                            [
                                EventName = uncleanedList{eventCounter}[eventDetailName],
                                EventDescription = uncleanedList{eventCounter}[eventDetailDescription],
                                EventSaleStatus = uncleanedList{eventCounter}[eventDetailStatus],
                                EventFirstDate = uncleanedList{eventCounter}[eventDetailFirst],
                                EventLastDate = uncleanedList{eventCounter}[eventDetailLast],
                                PaymentID = uncleanedList{eventCounter}[eventPaymentDetails][refs]{paymentCounter}[id],
                                PaymentName = uncleanedList{eventCounter}[eventPaymentDetails][refs]{paymentCounter}[name],
                                PaymentCreated = uncleanedList{eventCounter}[eventPayments]{paymentCounter}[paymentDetail][created],
                                CustomerEmail = uncleanedList{eventCounter}[eventPayments]{paymentCounter}[paymentDetail][customer][emailAddress],
                                CustomerFirstName = uncleanedList{eventCounter}[eventPayments]{paymentCounter}[paymentDetail][customer][firstName],
                                CustomerLastName = uncleanedList{eventCounter}[eventPayments]{paymentCounter}[paymentDetail][customer][lastName],
                                CustomerPhone = uncleanedList{eventCounter}[eventPayments]{paymentCounter}[paymentDetail][customer][mobilePhone],
                                PaymentStatus = uncleanedList{eventCounter}[eventPayments]{paymentCounter}[paymentDetail][status],
                                PaymentTotal = uncleanedList{eventCounter}[eventPayments]{paymentCounter}[paymentDetail][totalPrice][value]
                            ]
                        })
                    else
                        finalList = @recursiveCollapse(uncleanedList, eventCounter + 1, 0, finalList)
                in
                    finalList
            else
                finalList
        in
            finalList,
    dataTable = recursiveCollapse(allEventsLinks, 0, 0, {})
in
    dataTable

На этом этапе "dataTable" просто возвращается как пустая таблица.

Я считаю, что проблема связана с тем, что «finalList» не возвращается правильно через рекурсивные вызовы функции. У M нет ключевого слова return, поэтому я не знаю, что делать дальше.

Любая помощь приветствуется.

Спасибо


recursion powerbi m
person Verpos    schedule 08.08.2018    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Я понял.

Всем, кому нужна помощь в этом, вот мое решение:

recursiveCollapse = (uncleanedList as list, eventCounter as number, paymentCounter as 
number, finalList as list) =>
    let
        returnList = 
            let
                eventLength = List.Count(uncleanedList),
                eventIf = if eventCounter < eventLength then
                    let
                        eventReturn = if paymentCounter + 1 < List.Count(uncleanedList{eventCounter}[eventPayments]) then
                            let
                            addRow = 
                                finalList & 
                                {
                                    [
                                        EventName = uncleanedList{eventCounter}[eventDetailName],
                                        EventDescription = uncleanedList{eventCounter}[eventDetailDescription],
                                        EventSaleStatus = uncleanedList{eventCounter}[eventDetailStatus],
                                        EventFirstDate = uncleanedList{eventCounter}[eventDetailFirst],
                                        EventLastDate = uncleanedList{eventCounter}[eventDetailLast],
                                        PaymentID = uncleanedList{eventCounter}[eventPaymentDetails][refs]{paymentCounter + 1}[id],
                                        PaymentName = uncleanedList{eventCounter}[eventPaymentDetails][refs]{paymentCounter + 1}[name],
                                        PaymentCreated = uncleanedList{eventCounter}[eventPayments]{paymentCounter + 1}[paymentDetail][created],
                                        CustomerEmail = uncleanedList{eventCounter}[eventPayments]{paymentCounter + 1}[paymentDetail][customer][emailAddress],
                                        CustomerFirstName = uncleanedList{eventCounter}[eventPayments]{paymentCounter + 1}[paymentDetail][customer][firstName],
                                        CustomerLastName = uncleanedList{eventCounter}[eventPayments]{paymentCounter + 1}[paymentDetail][customer][lastName],
                                        CustomerPhone = uncleanedList{eventCounter}[eventPayments]{paymentCounter + 1}[paymentDetail][customer][mobilePhone],
                                        PaymentStatus = uncleanedList{eventCounter}[eventPayments]{paymentCounter + 1}[paymentDetail][status],
                                        PaymentTotal = uncleanedList{eventCounter}[eventPayments]{paymentCounter + 1}[paymentDetail][totalPrice][value]
                                    ]
                                },
                            recursion = @recursiveCollapse(uncleanedList, eventCounter, paymentCounter + 1, addRow)
                        in
                            recursion
                    else
                        let
                            recursion = @recursiveCollapse(uncleanedList, eventCounter + 1, 0, finalList)
                        in
                            recursion
                in
                    eventReturn
            else
                finalList
        in
            eventIf
in
    returnList,

dataTable = Table.FromList(recursiveCollapse(allEventsLinks, 0, 0, {}), Record.FieldValues, {
    "EventName",
    "EventDescription",
    "EventSaleStatus",
    "EventFirstDate",
    "EventLastDate",
    "PaymentID",
    "PaymentName",
    "PaymentCreated",
    "CustomerEmail",
    "CustomerFirstName",
    "CustomerLastName",
    "CustomerPhone",
    "PaymentStatus",
    "PaymentTotal"
})
in
dataTable
person Verpos    schedule 08.08.2018