How to transpose column value into header

[Jagen007]

Greeting

This is my table design is as below.

Order No.	Material	Delivery no.
ABC-11	245678	400000000
ABC-11	245678	500000000
ABC-11	245678	600000000
BBC-12	112233	200000000
BBC-12	112233	400000000

My desired output is in this table format.

Order No.	Material	Delivery no.	Delivery no.	Delivery no.
ABC-11	245678	400000000	500000000	600000000
BBC-12	112233	200000000	400000000

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

[spinfuzer]

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"

[wdx223_Daniel]

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])