Solved: Transposing only one column

Lheyman · ‎09-07-2023

The input.

the attempted output

I have tried creating individual tables with indexed values as shown below

The Table that i create after, still does not accept the proper formating

Please help!

Anonymous · ‎09-11-2023

Hi @Lheyman ,

You can use pivot to solve it.

For the Fee column, create a index group by Household column. Remove other columns and then use the pivot column feature.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldFBCoMwEAXQq0igOwnNHw9QCYJCbaCUdiEuu+2im16/EyZqUlJUEUadn5chDoNqW6NKVVt7Kwxf/BwKNwr3eT3fRXhzj0tzNaemr7uztq7nr3KbgxrLXwkiYZ+kzTGHkWC0EVsyiDJJxGhkd6pkp2oeaZNFs4XJAuDdULiRTI31w4wliIR9UnSYNGFE5NeFwo0Eo/WxvHTn5RAF+5Q/I5FgtBFbMogyyP3g8Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Household = _t, #"Account Name" = _t, #"Account #" = _t, #"Account Owner 1" = _t, #"Owner Email 1" = _t, #"Account Owner 2" = _t, #"Owner Email 2" = _t, Fee = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Household"}, {{"Count", each _, type table [Household=nullable text, Account Name=nullable text, #"Account #"=nullable text, Account Owner 1=nullable text, Owner Email 1=nullable text, Account Owner 2=nullable text, Owner Email 2=nullable text, Fee=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Account Name", "Account #", "Account Owner 1", "Owner Email 1", "Account Owner 2", "Owner Email 2", "Fee", "Index"}, {"Account Name", "Account #", "Account Owner 1", "Owner Email 1", "Account Owner 2", "Owner Email 2", "Fee", "Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Account Name", "Account #", "Account Owner 1", "Owner Email 1", "Account Owner 2", "Owner Email 2"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Index", type text}}, "en-US")[Index]), "Index", "Fee")
in
    #"Pivoted Column"

Use the same method for the Account # column and Account Name column, and then merge them.

Hope it helps.

Pivot columns - Power Query | Microsoft Learn

Merge queries overview - Power Query | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

slorin · ‎09-11-2023

Hi

Another solution

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldFBCoMwEAXQq0igOwnNHw9QCYJCbaCUdiEuu+2im16/EyZqUlJUEUadn5chDoNqW6NKVVt7Kwxf/BwKNwr3eT3fRXhzj0tzNaemr7uztq7nr3KbgxrLXwkiYZ+kzTGHkWC0EVsyiDJJxGhkd6pkp2oeaZNFs4XJAuDdULiRTI31w4wliIR9UnSYNGFE5NeFwo0Eo/WxvHTn5RAF+5Q/I5FgtBFbMogyyP3g8Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Household = _t, #"Account Name" = _t, #"Account #" = _t, #"Account Owner 1" = _t, #"Owner Email 1" = _t, #"Account Owner 2" = _t, #"Owner Email 2" = _t, Fee = _t]),
  Group = Table.Group(Source, {"Household"}, 
  {{"Account Owner 1", each Text.Combine(List.Distinct([Account Owner 1]))},
   {"Owner Email 1", each Text.Combine(List.Distinct([Owner Email 1]))},
   {"Account Owner 2", each Text.Combine(List.Distinct([Account Owner 2]))},
   {"Owner Email 2", each Text.Combine(List.Distinct([Owner Email 2]))},
   {"Data", each #table(null, {List.Combine(Table.ToRows(Table.FromColumns({[Account Name], [#"Account #"], [Fee]})))})}
   }),
  Expand = Table.ExpandTableColumn(Group, "Data", 
    {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"},
    {"Account Name1", "Account #1", "Fee1","Account Name2", "Account #2", "Fee2","Account Name3", "Account #3", "Fee3","Account Name4", "Account #4", "Fee4"})
in
Expand

Stéphane