In Power Query M I am trying to create a recursive function that will turn a mess of multidimensional lists and records into one flat list of records, so that the records can be easily manipulated in PowerBI.
I have worked with recursion in other languages but I am quite new to using M.
The mess of lists and records is similar in structure to this:
There are some minor differences but they shouldn't matter.
I am hoping the output will be similar to this:
{
[event1, eventDetail1, payment1, paymentDetails1],
[event1, eventDetail1, payment1, paymentDetails2],
[event1, eventDetail1, payment1, paymentDetails3],
[event1, eventDetail1, payment2, paymentDetails1],
}
Continuing on for every single item.
This is the recursive function I have currently:
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
At this stage "dataTable" is just returned as an empty table.
I believe the problem is due to the "finalList" not being returned correctly through the recursive calls of the function. M does not have a return keyword, so I am lost on what to do from here.
Any help is appreciated.
Thanks
I figured it out.
To anyone else who needs help with this here is my solution:
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