Flatten Rows in a Table for a Listbox

[Qwest336]

Hello everyone!

I have been trying to find a way to do this for a while now and I can't come up with anything good. As always, any help pointing me on the right path would be greatly appreciated.

What I need to do is to flatten multiple rows into one row, keeping non-empty cells in specific columns. For example, given the following recordset:
Player,Carries,Rushing Yards, Rushing TDs,Receptions,Receiving Yards,Receiving TDs
R1: Joe Jones,100,425,3,,,
R2: Joe Jones,,,,12,45,0

I would like to produce the following single row:

R1: Joe Jones,100,425,3,12,45,0

The only real difference is the data for merge is not numerical, it's textual.

I currently have the data in a table that I've loaded to the worksheet from PQ. The best solution would be one where I perform the merge operation in PQ/M-code prior to the table load as I'm filtering/loading a listbox on a userform from that worksheet. The next best solution would be to copy the data from the filtered table into a separate worksheet and then perform the merge operation prior to loading it into the Listbox on the Userform.

I wish I had some examples of what I have tried but I really haven't even gotten close yet. I almost got there using a Pivot Table and the Max operation, but the actual values, as mentioned, are textual in nature so they don't play well in the value section of a Pivot.

I'm using Excel 2013, 32bit.

Any thoughts?

 

[Qwest336]

Hello all,

I was able to finally find a method to flatten the table using PowerQuery and adapt it for my needs! For anyone that is looking, here's the link I used.

User "ImkeF" provided the following M-Code:

let
    Source = YourTable,
    #"Grouped Rows" = Table.Group(Source, {"YOURCOMMONFIELDNAME"}, {{"FillUp", each Table.FirstN(Table.FillUp(_,Table.ColumnNames(_)), 1), type table}}),
    #"Expanded FillUp" = Table.ExpandTableColumn(#"Grouped Rows", "FillUp", List.Skip(Table.ColumnNames(Source),1), List.Skip(Table.ColumnNames(Source),1))
in
    #"Expanded FillUp"