Merging Data Values From Separate Rows - Based On Conditions

[Rana Gray]

Hey Everyone! I'm fairly new to Power Query and I have managed alright so far but I need help making my process more efficient without sacrificing the specific data values I need represented.
**Sorry in advance for the crazy amount of columns, I promise they all serve a purpose.**

Data is auto filled as a new table row from MS FORMS >> Power Automate >> Excel Table. Not every field is required (some are branches on the form). What I require is the "Credit Specialist" field and the "CCAPS # (APPS)" field to match up from whatever row they are on and fuse the info so the values that exist fill the blank fields, and if there is a newer value (I have it Table.Buffer sorted in chronological order so newest is on top) to take the most recent value.

The complicated part is that certain fields may have an entry in the previous line but have been updated to remove something in the newer line. (ex. "Secondary Product" may have had an entry on the original line, but removed on the new updated line)

I have a query that is Grouped By perfectly executing what I need, but as you know once high volume of entries come in it's a very resource intensive command (I have pasted the syntax for the Group By conditions below for reference on what I need to do).

If I could get some recommendation on how I can better execute what I need in a more resource friendly way I would greatly appreciate any input. Thanks in advance!

GOUP BY SCRIPT:

= Table.Group(#"Changed Type2", {"Credit Specialist:"}, {{"Start Date/Time:", each List.Max([#"Start Date/Time"]), type nullable datetime}, {"End Date/Time:", each List.Max([#"End Date/Time"]), type nullable datetime}, {"CCAPS #:", each List.Max([#"CCAPS # (APPS):"]), type nullable text}, {"Joint Application:", each List.First([#"Joint Application:"]), type nullable text}, {"Co-Application:", each List.First([#"Co-Application:"]), type nullable text}, {"Co-Application #:", each List.First([#"Co-Application #:"]), type any}, {"Application Source:", each List.Max([#"Application Source:"]), type any}, {"Main Product:", each List.First([#"Main Product:"]), type nullable text}, {"Main Product Amount:", each List.First(List.RemoveNulls([#"Amount Requested (Product 1):"])), type nullable number}, {"Secondary Product:", each List.First([#"Secondary Product:"]), type nullable text}, {"Secondary Product Amount:", each List.First(List.RemoveNulls([#"Amount Requested (Product 2):"])), type any}, {"Application Decision:", each List.First([#"Application Decision:"]), type nullable text}, {"Approved With Cross-Sell (1):", each List.Max([#"Approved With Cross-Sell:"]), type nullable text}, {"Cross-Sell Product (1):", each List.Max([#"Cross-Sell Product (1):"]), type nullable text}, {"Cross-Sell Amount (1):", each List.Max([#"Cross-Sell Amount (1):"]), type nullable number}, {"Approved With Cross-Sell (2):", each List.Max([#"Second Cross-Sell:"]), type nullable text}, {"Cross-Sell Product(2):", each List.Max([#"Cross-Sell Product (2):"]), type any}, {"Cross-Sell Amount (2):", each List.Max([#"Cross-Sell Amount (2):"]), type any}, {"Approved With Cross-Sell (3):", each List.Max([#"Third Cross-Sell:"]), type any}, {"Cross-Sell Product (3):", each List.Max([#"Cross-Sell Product (3):"]), type any}, {"Cross-Sell Amount (3):", each List.Max([#"Cross-Sell Amount (3):"]), type any}, {"Adjudication Type:", each List.Last([#"Adjudication Type:"]), type nullable text}, {"Notes:", each Text.Combine([#"Additional Notes:"], " | "), type text}})

Credit Specialist:

BFS

Joint Application:

Co-Application:

Co-Application #:

CCAPS # (APPS):

Application Status:

Application Source:

Main Product:

Amount Requested (Product 1):

Secondary Product:

Amount Requested (Product 2):

Application Decision:

Approved With Cross-Sell:

Cross-Sell Product (1):

Cross-Sell Amount (1):

Second Cross-Sell:

Cross-Sell Product (2):

Cross-Sell Amount (2):

Third Cross-Sell:

Cross-Sell Product (3):

Cross-Sell Amount (3):

Adjudication Type:

Additional Notes:

Start Date/Time:

End Date/Time:

 

[alex78]

Hi,

Data would be useful...

A = {Table.Column, List.RemoveNulls, Table.ColumnNames(#"Changed Type2"){0}},
B = {
     (x,y)=> List.First(A{1}(A{0}(x,y))), 
     (x,y)=> List.Max(A{0}(x,y)), 
     (x,y)=> List.Last(A{1}(A{0}(x,y))), 
     (x,y)=> Text.Combine(A{0}(x,y), " | ")
    },
C = List.Buffer({
     {B{1}, "Start Date/Time:"},
     {B{1}, "End Date/Time:"},
     {B{1}, "CCAPS # (APPS):"}, 
     {B{0}, "Joint Application:"},
     {B{0}, "Co-Application:"},
     {B{1}, "Application Source:"},
     {B{0}, "Main Product:"},
     {B{0}, "Amount Requested (Product 1):"},
     {B{0}, "Secondary Product:"},
     {B{0}, "Amount Requested (Product 2):"},
     {B{0}, "Application Decision:"},
     {B{1}, "Approved With Cross-Sell:"},
     {B{1}, "Cross-Sell Product (1):"},
     {B{1}, "Cross-Sell Amount (1):"},
     {B{1}, "Second Cross-Sell:"},
     {B{1}, "Cross-Sell Product (2):"},
     {B{1}, "Cross-Sell Amount (2):"},
     {B{1}, "Third Cross-Sell:"}, 
     {B{1}, "Cross-Sell Product (3):"},
     {B{1}, "Cross-Sell Amount (3):"},
     {B{2}, "Adjudication Type:"},
     {B{3}, "Additional Notes:"}
    }),
D = {
     {"CCAPS # (APPS):", "CCAPS #:"},
     {"Amount Requested (Product 1):", "Main Product Amount:"}, 
     {"Amount Requested (Product 2):", "Secondary Product Amount:"}, 
     {"Approved With Cross-Sell:", "Approved With Cross-Sell (1):"}, 
     {"Second Cross-Sell:", "Approved With Cross-Sell (2):"}, 
     {"Third Cross-Sell:", "Approved With Cross-Sell (3):"}, 
     {"Additional Notes:", "Notes:"}
    },
E = Table.Combine(
    Table.Group(#"Changed Type2", A{2}, {"x", each Table.FromRows({{Table.FirstValue(_)} & 
    List.Accumulate(C, {}, (s,c)=> s & {c{0}(_, c{1})})}, List.ReplaceMatchingItems({A{2}} & List.Zip(C){1},D))}
    )[x])
in E

Regards

 

[Rana Gray]

Hi,

Data would be useful...

A = {Table.Column, List.RemoveNulls, Table.ColumnNames(#"Changed Type2"){0}},
B = {
     (x,y)=> List.First(A{1}(A{0}(x,y))),
     (x,y)=> List.Max(A{0}(x,y)),
     (x,y)=> List.Last(A{1}(A{0}(x,y))),
     (x,y)=> Text.Combine(A{0}(x,y), " | ")
    },
C = List.Buffer({
     {B{1}, "Start Date/Time:"},
     {B{1}, "End Date/Time:"},
     {B{1}, "CCAPS # (APPS):"},
     {B{0}, "Joint Application:"},
     {B{0}, "Co-Application:"},
     {B{1}, "Application Source:"},
     {B{0}, "Main Product:"},
     {B{0}, "Amount Requested (Product 1):"},
     {B{0}, "Secondary Product:"},
     {B{0}, "Amount Requested (Product 2):"},
     {B{0}, "Application Decision:"},
     {B{1}, "Approved With Cross-Sell:"},
     {B{1}, "Cross-Sell Product (1):"},
     {B{1}, "Cross-Sell Amount (1):"},
     {B{1}, "Second Cross-Sell:"},
     {B{1}, "Cross-Sell Product (2):"},
     {B{1}, "Cross-Sell Amount (2):"},
     {B{1}, "Third Cross-Sell:"},
     {B{1}, "Cross-Sell Product (3):"},
     {B{1}, "Cross-Sell Amount (3):"},
     {B{2}, "Adjudication Type:"},
     {B{3}, "Additional Notes:"}
    }),
D = {
     {"CCAPS # (APPS):", "CCAPS #:"},
     {"Amount Requested (Product 1):", "Main Product Amount:"},
     {"Amount Requested (Product 2):", "Secondary Product Amount:"},
     {"Approved With Cross-Sell:", "Approved With Cross-Sell (1):"},
     {"Second Cross-Sell:", "Approved With Cross-Sell (2):"},
     {"Third Cross-Sell:", "Approved With Cross-Sell (3):"},
     {"Additional Notes:", "Notes:"}
    },
E = Table.Combine(
    Table.Group(#"Changed Type2", A{2}, {"x", each Table.FromRows({{Table.FirstValue(_)} &
    List.Accumulate(C, {}, (s,c)=> s & {c{0}(_, c{1})})}, List.ReplaceMatchingItems({A{2}} & List.Zip(C){1},D))}
    )[x])
in E

Regards

Thank you for this - the data set is sensitive information that's why it was excluded. I know it makes any response more challenging. I will play around with this and update