Power Query M Secara Rekursif Membuat Daftar Datar dari Daftar Multidimensi

Di Power Query M saya mencoba membuat fungsi rekursif yang akan mengubah daftar dan catatan multidimensi yang berantakan menjadi satu daftar catatan datar, sehingga catatan dapat dengan mudah dimanipulasi di PowerBI.

Saya telah bekerja dengan rekursi dalam bahasa lain tetapi saya cukup baru dalam menggunakan M.

Kekacauan daftar dan catatan memiliki struktur yang mirip dengan ini:

  • Event
    • Event Details
      • Payments
        • Payment Details

Ada beberapa perbedaan kecil tetapi itu tidak menjadi masalah.

Saya berharap hasilnya akan serupa dengan ini:

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

Melanjutkan untuk setiap item.

Ini adalah fungsi rekursif yang saya miliki saat ini:

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

Pada tahap ini "dataTable" baru saja dikembalikan sebagai tabel kosong.

Saya yakin masalahnya adalah karena "finalList" tidak dikembalikan dengan benar melalui panggilan fungsi secara rekursif. M tidak memiliki kata kunci return, jadi saya bingung apa yang harus saya lakukan dari sini.

Bantuan apa pun dihargai.

Terima kasih


recursion powerbi m
person Verpos    schedule 08.08.2018    source sumber

Jawaban (1)


arrow_upward
0
arrow_downward

Saya menemukan jawabannya.

Bagi siapa pun yang membutuhkan bantuan dalam hal ini, inilah solusi saya:

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