Forum Discussion

Jagen007's avatar
Jagen007
Regular Visitor
2 years ago

How to transpose column value into header

Greeting

This is my table design is as below.

Order No.MaterialDelivery no.
ABC-11245678400000000
ABC-11245678500000000
ABC-11245678600000000
BBC-12112233200000000
BBC-12112233400000000

 

My desired output is in this table format.

Order No.MaterialDelivery no.Delivery no.Delivery no.
ABC-11245678400000000500000000600000000
BBC-12112233200000000400000000 

 

Kindly please help to advise how to solve this in Power Query or Power BI.

 

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion

    NewStep=Table.Combine(Table.Group(YourTable,{"Order No.","Material"},{"n",each #table({"Order No.","Material"}&List.Transform({1..Table.RowCount(_)},each "Deliver no. "&Text.From(_)),{{[#"Order No."]{0},[Material]{0}}&[#"Deliver no."]})})[n])

  • The easiest way to do it (but not necessarily the fastest or dynamic) is to

    1) Group By Order No. and Material  With Operation All Rows and name is "All"

    2) Add custom column named with formula:  Table.Transpose(Table.SelectColumns([All], "Delivery no."))

    3) Expand and rename your columns

     

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1jU0VNJRMjIxNTO3ADJMDKBAKVYHm7wpAXkzFHknkLwRUNjQ0MjI2BikkIA8kv2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order No." = _t, Material = _t, #"Delivery no." = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order No.", type text}, {"Material", Int64.Type}, {"Delivery no.", Int64.Type}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Order No.", "Material"}, {{"All", each _, type table [#"Order No."=nullable text, Material=nullable number, #"Delivery no."=nullable number]}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose(Table.SelectColumns([All], "Delivery no."))),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
    in
        #"Expanded Custom"