Forum Discussion
Solved
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 ...
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"
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
Resolver IV
Resolver IVTry 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