Unpivot only a single column of a repeating groups of rows

Power Query
1

Hi All,
I had found a solved topic below I thought I could use to solve an issue I’m trying to resolve.

I have a table I need to pivot the values of a single column. I tried to use the Record.ToList(_) didn’t work as I expected. Book1.xlsx (18.4 KB) unpivoting.pbix (25.5 KB)
image

Can the same approach work?

Thanks in advance

Roberto

2

Hi @Roberto,

Thanks for posting your question.

You can copy the full M script here, into a new blank query.

let
    MaxOwnerCount = List.Max( List.Buffer( GroupRows[RowCount] )),
    NewColumns = List.Transform( {1..MaxOwnerCount}, each "Owner " & Text.From(_) ),

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoA4kggNjYxAJJZKfmpDpl5yXrJ+blKsTrYlKQl5SQmZxNQlJWSmIdqkBNQNByqztDIGLtBmIowDXIGikYBcSBIjQEWy2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Org = _t, Repo = _t, Type = _t, Findings = _t, owner = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Org", type text}, {"Repo", type text}, {"Type", type text}, {"Findings", Int64.Type}, {"owner", type text}}),
    GroupRows = Table.Group(ChType, {"Org", "Repo", "Type", "Findings"}, {{"RowCount", each Table.RowCount(_), Int64.Type}, {"AllRows", each _, type table [Org=nullable text, Repo=nullable text, Type=nullable text, Findings=nullable number, owner=nullable text]}}),
    AddOwner = Table.AddColumn(GroupRows, "Owner", each Text.Combine( [AllRows][owner], "|" )),
    SplitColumn = Table.SplitColumn(AddOwner, "Owner", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), NewColumns ),
    CleanUpColumns = Table.RemoveColumns(SplitColumn,{"AllRows", "RowCount"})
in
    CleanUpColumns

.
For your requirement you can use the Group By option.
and extract the Owners from that nested table before splitting that into separate columns.

To dynamically create the required number of columns I’ve added 2 steps, before the Source step in this query, this way you can avoid hard coding new column names.

with this result.

image
image1164×99 6.81 KB

.

I hope this is helpful.

3 Likes
3

Hi @Melissa thanks for this code, it worked perfectly ( I had no doubt :slight_smile: )
I like how you use the lazy evaluation and putting variables in the first section.
I need still to understand how to mentally disassemble data when it gets a bit more complex (table in cells), to use M functions correctly.
Eager to watch one of your next videos on PQ!

Thanks a lot

Roberto

1 Like
5

Hi @sprmnt21,
thanks for your solution.

Regards

Roberto

6 
let  
Origine = Excel.Workbook(File.Contents("C:\Users\xxxxxx\DNA-Unpivot only a single column of a repeating groups of rows.xlsx"), null, true),
source_Table = Origine{[Item="source",Kind="Table"]}[Data],
#"Raggruppate righe" = Table.Group(source_Table, {"Org", "Repo", "Type", "Findings"}, {{"owner", each [owner]}}),
#"Valori estratti" = Table.TransformColumns(#"Raggruppate righe", {"owner", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
#"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Valori estratti", "owner", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"owner.1", "owner.2", "owner.3"})
in #"Suddividi colonna in base al delimitatoreindent preformatted text by 4 spaces

`