Reorder rows

[adriano321souza]

Good Morning, I have a database where the protocol number is repeated in multiple lines according to the subprocesses of the service, so I would like each protocol to appear in only one line and its processes be shown in different columns. How can I do this

[BekahLoSurdo]

Hi @adriano321souza,

Can you confirm that what you're looking for is to have a column header for each possible sub-process? If that is the case, the intersections between each row (protocol number) and each sub-process column would show a binary TRUE-FALSE value depending on whether that protocol had that sub-process: 

Let me know if that's what you'd like or if you're looking for something else. 

[adriano321souza]

Hello,

---

@BekahLoSurdo wrote:

Hi @adriano321souza,

Can you confirm that what you're looking for is to have a column header for each possible sub-process? If that is the case, the intersections between each row (protocol number) and each sub-process column would show a binary TRUE-FALSE value depending on whether that protocol had that sub-process: 

Let me know if that's what you'd like or if you're looking for something else. 

---

the subprocesses should appear in the same line as the protocol and the client.

[BekahLoSurdo · Accepted solution]

Try this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\rlosurdo\Desktop\SampleData_Process.xlsx"), null, true),
    Process_Sheet = Source{[Item="Process",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Process_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Protocol", Int64.Type}, {"Client", type text}, {"Process", type text}, {"Sub-process", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Protocol"}, {{"All", each Table.AddIndexColumn(_, "Sub-processQty",1,1)}}, GroupKind.Local),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Client", "Process", "Sub-process", "Sub-processQty"}, {"Client", "Process", "Sub-process", "Sub-processQty"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "ColumnHeaders", each "Sub-process #" & Number.ToText([#"Sub-processQty"])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Sub-processQty"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[ColumnHeaders]), "ColumnHeaders", "Sub-process")
in
    #"Pivoted Column"

[BekahLoSurdo]

Hi @adriano321souza,

Was that what you were looking for or did you need something else?