Use column values as column headings to sort data

[joooffice]

Hi 

I have a data table like the following:

submission id service name person type
1 service a bob adult
2 service b joe adult
3 service c sam child
4 service c ella child
5 service a rob adult
6 service b tim child
7 service c william adult
8 service c harry adult
9 service a bella adult
10 service b paul child

I want to transpose the data so the the data in the service column become the column headings and show the data from the other fields below... so service a would look like 

service A
submission id name person type
1 bob adult
5 rob adult
9 bella adult

Ideally the services would appear in the same row with duplicated sets of columns for the other data 

service A service b service c
submission id name person type submission id name person type submission id name person type
1 bob adult 2 joe adult 3 sam child
5 rob adult 6 tim child 4 ella child
9 bella adult 10 paul child 7 william adult
8 harry adult

How can I do this in power query M not Dax? I think it should be relatively simple but I can't work it out. 

[Anonymous]

Here is a proposal, although the specification of the name of the columns is not very clear.
I hope it is relatively simple enough, as you expected.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSpOLSrLTE4FshKBOCk/CcRKKc0pUYrViVYyQlEBksvKT0VRYYyiIhnES8wFsTIyc1LAKkwwVKTm5CSiKDHFcEgRmkPMMBxSkolqjTmGNeWZOTmZYMcgzLHAUJWRWFRUiaLGEjNYoC5GqDE0wHBQQWJpDsJFsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [submission = _t, id = _t, service = _t, name = _t, #"type" = _t]),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"id", "service", "submission", "name", "type"}),
    ser=List.Distinct(#"Reordered Columns"[service]),
    cols=List.RemoveFirstN(Table.ColumnNames(#"Reordered Columns"),2),
    ncols=List.Combine(List.TransformMany(ser, (e)=> {cols},(x,y)=>List.Transform(y, each x&" service "&Text.From(_)))) ,

    #"Grouped Rows" = Table.FromColumns( List.Combine(Table.Group(#"Reordered Columns", {"id", "service"}, {{"all", each Table.ToColumns(_[[submission],[name],[type]])}})[all]),ncols)
in
    #"Grouped Rows"

[Anonymous]

Although I have tried to format the column text to have two lines, it seems that the format "# (lf)" is not recognized in the column header.
Anyone know if there is a way to format the header?

[Anonymous]

If we had a generalized sort of List.Zip,

ListZip

= (lists)=>
let
   ncol=List.Count(lists)-1,
   lzz= List.Zip(List.Zip(lists)),
   nl=List.Transform({0..ncol},each List.Transform(lzz{_}, (e)=> e ?? List.Repeat({null},List.Count(lzz{_}{0}))))
in
   nl

we could do this "particular" transformation in a much more concise way

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSpOLSrLTE4FshKBOCk/CcRKKc0pUYrViVYyQlEBksvKT0VRYYyiIhnES8wFsTIyc1LAKkwwVKTm5CSiKDHFcEgRmkPMMBxSkolqjTmGNeWZOTmZYMcgzLHAUJWRWFRUiaLGEjNYoC5GqDE0wHBQQWJpDsJFsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [submission = _t, id = _t, service = _t, name = _t, #"type" = _t]),
    #"Reordered Columns1" = Table.ReorderColumns(Source,{"id", "service", "submission", "name", "type"}),




    #"Grouped Rows" = Table.FromList(List.Zip(ListZip(Table.Group(#"Reordered Columns1", {"id","service"}, {{"snt", each Table.ToRows(Table.DemoteHeaders(_[[submission],[name],[type]]))}})[snt])), List.Combine),
    #"Promoted Headers" = Table.PromoteHeaders(#"Grouped Rows", [PromoteAllScalars=true])
in
    #"Promoted Headers"

[joooffice · Accepted solution]

Thanks i ended up doing separate queries for each service so i could load independently into my spreadshset and make it lay out like I needed.

[Icey]

Hi @joooffice ,

Glad to hear the issue is gone. Please accept your reply or the replies making sense as solution above as the solution. Your contribution is highly appreciated.

Best Regards,

Icey

[Anonymous]

Is there any particular reason why you want to do such a horrible thing from the point of view of data modeling?

[Jimmy801]

Hello @joooffice 

Apply a Table.Group to your service column and apply there some Table.ToRows in combination of a List.Transform and a Text.combine. Use the outcome to create your table with a Table.FromColumns. Here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY/NCoNADITfJWcP2vrTPsviIa4LRlIsW6349q4ihPESBr5hZuIcFZTRL8S/+MBJdlOXLveLztRmjh6GDzBOAfDTsD8kf9L1g2h/4hJxUGXgFZbHW3mN5bNgeoPpq6jKOcASXmgZOMYNDO/b+9dEMxQ5jvjyorai3QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [submissionid = _t, service = _t, name = _t, persontype = _t]),
    #"Grouped Rows" = Table.Group(Source, {"service"}, {{"AllRows", each List.Transform(Table.ToRows(Table.RemoveColumns(_, "service")), each Text.Combine(_,", "))}}), 
    CreateTable = Table.FromColumns(#"Grouped Rows"[AllRows], #"Grouped Rows"[service])
in
    CreateTable

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[joooffice]

Thanks Jimmy,

It works if I only have the service and the name column in the table but if i leave the other columns in it generates an error.

I need all the other coloums to be displayed in the same way even if that is a duplicated of the Service name in the column headings (i can then merge into one column)

[Jimmy801]

Hello @joooffice 

i cannot follow you. This code needs a table with a column name "service". All other columns are then grouped togheter (whatever name they have) and displayed as you requested. Replace the first two steps with your data source and you shoud be fine

All the best

Jimmy

[Greg_Deckler]

@joooffice - Well, you could use a Matrix visualization and skip both DAX and M, but otherwise, this is a pivot operation in Power Query. @ImkeF or @edhans can assist if that is what you want, but I would generally advise against it.