Forum Discussion

joooffice's avatar
joooffice
Helper I
5 years ago
Solved

Use column values as column headings to sort data

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. 

Need Help
  • joooffice's avatar
    joooffice
    5 years ago

    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.

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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.

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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's avatar
      joooffice
      Helper I
      5 years ago

      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's avatar
        Jimmy801
        Community Champion
        5 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    Is there any particular reason why you want to do such a horrible thing from the point of view of data modeling?
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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"