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"
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"