Forum Discussion
Reorder rows
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
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"
4 Replies
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.
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.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"
