Forum Discussion
ashikts
Helper II
Helper IItransformación de datos
hola señor,
ayer ha respondido a una de mis consultas de ayer. Pero eso no fue suficiente para mí.
Por favor, ayuda a resolverlo .
Te lo explicaré de nuevo.
| empleado-proyecto | horas lanzadas |
| ashik | 48 |
| pbi | 40 |
| Java | 8 |
| Rahul | 48 |
| Java | 32 |
| Php | 32 |
| licencia personal | 8 |
| día festivo | 8 |
| ese | 40 |
| Sql | 32 |
| día festivo | 8 |
nuestra tabla ;ike esto con diferente nombre de empleado y su proyecto de asignación de tiempo prudente y vacaciones o asignación de vacaciones.
tenemos otra mesa
| Empleado | total de horas echadas | empid |
| ashik | 48 | ina101 |
| Rahul | 48 | ina504 |
| ese | 40 |
|
resultado que quiero es . Por favor, ayúdame sir.im nuevo en este departamento
| Empleado | horas de vacaciones | horas de licencia personal |
| ashik | 8 | 0 |
| Rahul | 8 | 8 |
| ese | 8 | 0 |
No @ashikts ,
un poco de relleno hacia abajo y pivot-magic debe hacer el truco:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSizOyMxW0lEysVCK1YlWKkjKBHEMwJysxLJEIA8iU5SYUZqDUAiVMzaCaMsoQOKkFhXn5yXmKOSkJpalwg3IyM/JTEmshPMTczNLEHYVF+YgTEBRGgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"employee-project" = _t, #"hours casted" = _t]), EmployessTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSizOyMxW0lEysQASmXmJhgaGSrE60UpFiRmlOUjipgYmYPHE3MwSkLABkFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"employee " = _t, #"total casted hours" = _t, empid = _t]), #"Merged Queries" = Table.NestedJoin(Source, {"employee-project"}, EmployessTable, {"employee "}, "EmployessTable", JoinKind.LeftOuter), #"Expanded EmployessTable" = Table.ExpandTableColumn(#"Merged Queries", "EmployessTable", {"employee "}, {"employee "}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded EmployessTable",{{"hours casted", type number}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "employee ", "employee - Copy"), #"Filled Down" = Table.FillDown(#"Duplicated Column",{"employee "}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([#"employee - Copy"] = null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"employee - Copy"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"employee-project"]), "employee-project", "hours casted", List.Sum) in #"Pivoted Column"Compruebe también el archivo adjunto.
3 Replies
- amitchandak
Super User
Syndicate_AdminAdministrator
@ImkeF @amitchandak gracias, señor. Funcionó .it fue muy útil
- ImkeF
Community Champion
No @ashikts ,
un poco de relleno hacia abajo y pivot-magic debe hacer el truco:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSizOyMxW0lEysVCK1YlWKkjKBHEMwJysxLJEIA8iU5SYUZqDUAiVMzaCaMsoQOKkFhXn5yXmKOSkJpalwg3IyM/JTEmshPMTczNLEHYVF+YgTEBRGgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"employee-project" = _t, #"hours casted" = _t]), EmployessTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSizOyMxW0lEysQASmXmJhgaGSrE60UpFiRmlOUjipgYmYPHE3MwSkLABkFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"employee " = _t, #"total casted hours" = _t, empid = _t]), #"Merged Queries" = Table.NestedJoin(Source, {"employee-project"}, EmployessTable, {"employee "}, "EmployessTable", JoinKind.LeftOuter), #"Expanded EmployessTable" = Table.ExpandTableColumn(#"Merged Queries", "EmployessTable", {"employee "}, {"employee "}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded EmployessTable",{{"hours casted", type number}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "employee ", "employee - Copy"), #"Filled Down" = Table.FillDown(#"Duplicated Column",{"employee "}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([#"employee - Copy"] = null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"employee - Copy"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"employee-project"]), "employee-project", "hours casted", List.Sum) in #"Pivoted Column"Compruebe también el archivo adjunto.
