Transposing from columns to rows

[michellerob2000]

I need to move the names (col B) to rows assigned to the app group name - I've tried unpivot - I cannot work out how to do it.

For example

WP2CCAC - Kasia Barnert and Shelley Attfield on one row

Really would appreciate any advice on how to fix this, thank you in advance.

Michelle.

[AntrikshSharma]

@michellerob2000 Paste this code in the advanced editor:

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45WCg8wcnZ2dFbSUfJOLM5MVHBKLMpLLSpRitVBlgvOSM3JSa1UcCwpSctMzUlBljYwBCoISC1JLVJwzkgsyE3Mw5B1L0pMTlVwApqhFBsLAA==",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ #"App Group Name" = _t, Description = _t ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            { { "App Group Name", type text }, { "Description", type text } }
        ),
    GroupedRows = 
        Table.Group (
            ChangedType,
            { "App Group Name" },
            { { "Result", each Text.Combine ( _[Description], " and " ), type text } }
        )
in
    GroupedRows

[BA_Pete]

Hi Michelle,

Creating varying numbers of columns for each of your names would be a pain. If you just want to condense your table to one row per [App Group Name], but retain all the [Description] information, then I'd group the table on [App Group Name] and add a description aggregatd column using the SUM operator. This will give you an error initially, but will give you the code structure in the formula bar, where you can replace the SUM section with Text.Combine, something like this:

// Change this:
Table.Group(previousStepName, {"App Group Name"}, {{"Description", each List.Sum([Description]), type nullable text}})

// to this:
Table.Group(previousStepName, {"App Group Name"}, {{"Description", each Text.Combine([Description], ", ")}})

Pete

[michellerob2000]

Thank you Pete, that has worked for and saved me a lot of time.

[michellerob2000]

I don't know what I have done, but done a fresh one and just cannot group it, I wont give me the 2nd column in the grouping

Help! 

Michelle.

[BA_Pete]

Hi Michelle,

Not sure what the question is here.

If you select your [App Group Name] column, go to the Home tab > Group By, then add a SUM aggregated column on your [description] column that should get you back to where you can swap out the code as before.

Pete

[michellerob2000]

In the group by, it is not letting me click on the drop own for the sum and on the column dropdown it wont open up for me to select Description column

[BA_Pete]

Make sure that the Group By dialog box is in the centre of your PowerBI app window, not on the edge of it or just outside of it.

If this isn't the issue, then you may need to send screenshots/gifs of what's happening.

Pete