Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
103 | |
101 | |
87 | |
73 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |