Transpose/Pivot Table

[sperry1625]

I am trying to figure out how to transpose/Pivot a table I have.  Here is what I have:

SR Id	SR Name	Name	Type	Status	State	Comment
1	Mapping (9116)	[1] - 9116	Account	Complete	Approved	Comment 1
1	Mapping (9116)	[1] - 9116	Function	Complete	Approved	Comment2
1	Mapping (9116)	[1] - 9116	ICP	Complete	Approved	Comment3
1	Mapping (9116)	[1] - 9116	Entity	Complete	Approved	Comment4

Here is what I am trying to achieve:

SR Id

SR Name

Name

Account Status

Account State

Account Comment

Function Status

Function State

Function Comment

ICP Status

ICP State

ICP Comment

Entity Status

Entity State

Entity Comment

1

Mapping (9116)

[1] - 9116

Complete

Approved

Comment 1

Complete

Approved

Comment 2

Complete

Approved

Comment 3

Complete

Approved

Comment 4

I have tried several options; nothing gives me the result I am looking for. Any help would be appreciated.

Thank you.

Steven

[lance_6 · Accepted solution]

This is an interesting way to do it, but...

let
    Source = Source,
    #"Grouped Rows" = Table.Group(Source, {"id", "name", "type"}, {{"Rows", each _, type table [id=nullable number, name=nullable text, type=nullable text, status=nullable text, state=nullable text, comment=nullable text]}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"type"]), "type", "Rows"),
    #"Expanded Account" = Table.ExpandTableColumn(#"Pivoted Column", "Account", {"status", "state", "comment"}, {"Account.status", "Account.state", "Account.comment"}),
    #"Expanded Function" = Table.ExpandTableColumn(#"Expanded Account", "Function", {"status", "state", "comment"}, {"Function.status", "Function.state", "Function.comment"}),
    #"Expanded ICP" = Table.ExpandTableColumn(#"Expanded Function", "ICP", {"status", "state", "comment"}, {"ICP.status", "ICP.state", "ICP.comment"}),
    #"Expanded Entity" = Table.ExpandTableColumn(#"Expanded ICP", "Entity", {"status", "state", "comment"}, {"Entity.status", "Entity.state", "Entity.comment"})
in
    #"Expanded Entity"

So it's essentially a group by, then pivot, then expand.

[v-shex-msft · Accepted solution]

HI @sperry1625,

You can take a look at the following sample formulas if these suitable for your requirement:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNLCjIzEtX0LA0NDTTBApEG8Yq6CqAeECOY3JyfmleCZDlnJ9bkJNakgoSLCgoyi9LTYGI5qbmlSgYKsXqEGOeW2lecklmfh4hA42INM/TOYCQUcZEGuWaV5JZUknINBOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SR Id" = _t, #"SR Name" = _t, Name = _t, Type = _t, Status = _t, State = _t, Comment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SR Id", Int64.Type}, {"SR Name", type text}, {"Name", type text}, {"Type", type text}, {"Status", type text}, {"State", type text}, {"Comment", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Status", "State", "Comment"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"SR Id", "SR Name", "Name"}, {{"Count", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(_,{"Type","Merged"}))), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Account", "Function", "ICP", "Entity"}, {"Account", "Function", "ICP", "Entity"})
in
    #"Expanded Count"

Comment:

I merge three fields and use the group function to summary row table records. After these steps, I nested some functions to deal with group result fields to remove not-used fields and transpose them.

Regards,

Xiaoxin Sheng

[lance_6 · Accepted solution]

This is an interesting way to do it, but...

let
    Source = Source,
    #"Grouped Rows" = Table.Group(Source, {"id", "name", "type"}, {{"Rows", each _, type table [id=nullable number, name=nullable text, type=nullable text, status=nullable text, state=nullable text, comment=nullable text]}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"type"]), "type", "Rows"),
    #"Expanded Account" = Table.ExpandTableColumn(#"Pivoted Column", "Account", {"status", "state", "comment"}, {"Account.status", "Account.state", "Account.comment"}),
    #"Expanded Function" = Table.ExpandTableColumn(#"Expanded Account", "Function", {"status", "state", "comment"}, {"Function.status", "Function.state", "Function.comment"}),
    #"Expanded ICP" = Table.ExpandTableColumn(#"Expanded Function", "ICP", {"status", "state", "comment"}, {"ICP.status", "ICP.state", "ICP.comment"}),
    #"Expanded Entity" = Table.ExpandTableColumn(#"Expanded ICP", "Entity", {"status", "state", "comment"}, {"Entity.status", "Entity.state", "Entity.comment"})
in
    #"Expanded Entity"

So it's essentially a group by, then pivot, then expand.