Flatten Rows Based on Column Value

[MD_1385]

Issue:  Data needs to be moved from one database to another. Unfortunately I do not have access to either database directly, I only have access to what I can export. Database export gives me contacts that are related to an ID, however each contact is on a different row. Database we are importing to requires all contacts to be on one row.

We need to flatten data in a way that looks for similar user_id's, and if they match, move that data a new column up to, but no more than 4 times and keep all null data.

Obstacles: In the database we are exporting from, it is possible to have as many contacts assigned to a user id as you want. The database we are importing to has a max of 4 contacts per user_id.

The ultimate goal is to automate the process when a new export is detected.

exported Dataset

student_user_id,ec_firstname,ec_lastname,ec_phone,ec_email
4768086,John,Doe,5555555555,email@email.com
4768086,John,Doe,5555555555,email@email.com
4768308,John,Doe,5555555555,email@email.com
4768308,John,Doe,5555555555,email@email.com
4768308,John,Doe,5555555555,email@email.com
4858034,John,Doe,5555555555,email@email.com
4858034,John,Doe,5555555555,email@email.com
4858034,John,Doe,5555555555,email@email.com
4858034,John,Doe,5555555555,email@email.com
4925492,John,Doe,5555555555,email@email.com
4925492,John,Doe,5555555555,email@email.com
4925492,John,Doe,5555555555,email@email.com
4925492,John,Doe,5555555555,email@email.com
4925492,John,Doe,5555555555,email@email.com

Required Dataset to Import

student_user_id,ec_firstname,ec_lastname,ec_phone,ec_email,ec2_firstname,ec2_lastname,ec2_phone,ec2_email,ec3_firstname,ec3_lastname,ec3_phone,ec3_email,ec4_firstname,ec4_lastname,ec4_phone,ec4_email
4768086,John,Doe,5555555555,email@email.com,John,Doe,5555555555,email@email.com,,,,,,,,
4768308,John,Doe,5555555555,email@email.com,John,Doe,5555555555,email@email.com,John,Doe,5555555555,email@email.com,,,,
4858034,John,Doe,5555555555,email@email.com,John,Doe,5555555555,email@email.com,John,Doe,5555555555,email@email.com,John,Doe,5555555555,email@email.com
4925492,John,Doe,5555555555,email@email.com,John,Doe,5555555555,email@email.com,John,Doe,5555555555,email@email.com,John,Doe,5555555555,email@email.com

I figured Power Query is the best option, but if there is a better one out there, let me know. Most of the options I found when doing a search did not match what I needed, nor do I have the requisite Excel knowledge to modify an existing solution.

[edhans · Accepted solution]

Hi @MD_1385 ,

I converted this:

to this (not all columns showing because it is WIDE

This is the code - 3 steps including the source.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE3szCwMFPSUfLKz8gDUi75qUDSFA6AnNTcxMwcBzCpl5yfqxSrQ5k+YwOLwa/PwtTCwNhk+OqzNDIF4lF9yPpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [student_user_id = _t, ec_firstname = _t, ec_lastname = _t, ec_phone = _t, ec_email = _t]),
    #"Grouped Rows" = 
        Table.Group(
            Source, 
            {"student_user_id"}, 
            {
                {"All Rows", each 
                                Table.PromoteHeaders(
                                    Table.Transpose(
                                        Table.CombineColumns(
                                            Table.TransformColumnTypes(
                                                Table.UnpivotOtherColumns(
                                                    Table.AddIndexColumn(_, "Index", 0),
                                                    {"Index"},
                                                    "Attribute",
                                                    "Value"), 
                                                {
                                                    {"Index", type text}
                                                },
                                                "en-US"),
                                            {"Attribute", "Index"},
                                            Combiner.CombineTextByDelimiter("", QuoteStyle.None),
                                            "New Columns"
                                        )
                                    )
                                )
                }
            }
        ),
    FinalStep = Table.Combine(#"Grouped Rows"[All Rows])
in
    FinalStep

I did everything in a nested table grouping based on the student user id.

1. I added an index column. These will be used for new new column headers that have 0, 1, 2, etc.
2. I then unpivoted the table using the index column as the main column.
3. I then combined the index with the attribute, which is the old column name.
4. I then transposed the table again, and now it is a super wide table.
5. I promoted the first row (the new column names) to the headers.
6. The final step took all of those tables (one for each student Id with a varrying number of rows in them) and used Table.Combine.

You can convert the nulls to commas or whatever at this point.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[MD_1385]

@edhans Thanks so much for the detailed explination, this works perfectly!

[edhans]

Excellent @MD_1385 - glad I was able to help