Transpose/Pivot Table

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.

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