Forum Discussion
Helper IIIConvert Table Structure
Is posible in powerquery to convert this table
From this table
Código | Empleado | Marcaciones |
1 | Alejandro | 20/07/2018 8:00:00 |
1 | Alejandro | 20/07/2018 12:00:00 |
1 | Alejandro | 20/07/2018 13:00:00 |
1 | Alejandro | 20/07/2018 17:00:00 |
2 | José | 20/07/2018 8:00:00 |
2 | José | 20/07/2018 12:00:00 |
2 | José | 20/07/2018 13:00:00 |
2 | José | 20/07/2018 13:00:10 |
2 | José | 20/07/2018 17:00:00 |
To this table:
Código | Empleado | Fecha | Marcación 1 | Marcación 2 | Marcación 3 | Marcación 4 | Marcación 5 |
1 | Alejandro | 20/7/2018 | 8:00:00 | 12:00:00 | 13:00:00 | 17:00:00 |
|
2 | José | 20/7/2018 | 8:00:00 | 12:00:00 | 13:00:00 | 13:00:10 | 17:00:00 |
Thanks
Marco
Hi mrbajana ,
Try this mcode:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMSc1KzEspygeyjQz0Dcz1jQwMLRQsrAwMgEgpVgevMkMjItUZE6nOHEmdEVDOK7/48ErcTsOhBMVZuNQYE6vGEL8auJNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Código = _t, Empleado = _t, Marcaciones = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Código", Int64.Type}, {"Empleado", type text}, {"Marcaciones", type datetime}}),
#"Inserted Time" = Table.AddColumn(#"Changed Type", "Time", each DateTime.Time([Marcaciones]), type time),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Time",{{"Marcaciones", type date}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {"Marcaciones"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Only Selected Columns", {"Código", "Empleado"}, {{"Rows", each
Table.CombineColumns(
Table.TransformColumnTypes(
Table.AddIndexColumn(_, "Index", 1,1), {{"Index", type text}}
),
{"Attribute", "Index"},
Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),
"Merged"
), type table [Código=number, Empleado=text, Time=time, Attribute=text, Value=date, Merged=text]}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Rows"}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows", {"Código", "Empleado", "Time", "Value", "Merged"}, {"Código", "Empleado", "Time", "Value", "Merged"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Rows", List.Distinct(#"Expanded Rows"[Merged]), "Merged", "Time")
in
#"Pivoted Column"
1 Reply
camargos88Community Champion
Hi mrbajana ,
Try this mcode:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMSc1KzEspygeyjQz0Dcz1jQwMLRQsrAwMgEgpVgevMkMjItUZE6nOHEmdEVDOK7/48ErcTsOhBMVZuNQYE6vGEL8auJNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Código = _t, Empleado = _t, Marcaciones = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Código", Int64.Type}, {"Empleado", type text}, {"Marcaciones", type datetime}}),
#"Inserted Time" = Table.AddColumn(#"Changed Type", "Time", each DateTime.Time([Marcaciones]), type time),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Time",{{"Marcaciones", type date}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {"Marcaciones"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Only Selected Columns", {"Código", "Empleado"}, {{"Rows", each
Table.CombineColumns(
Table.TransformColumnTypes(
Table.AddIndexColumn(_, "Index", 1,1), {{"Index", type text}}
),
{"Attribute", "Index"},
Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),
"Merged"
), type table [Código=number, Empleado=text, Time=time, Attribute=text, Value=date, Merged=text]}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Rows"}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows", {"Código", "Empleado", "Time", "Value", "Merged"}, {"Código", "Empleado", "Time", "Value", "Merged"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Rows", List.Distinct(#"Expanded Rows"[Merged]), "Merged", "Time")
in
#"Pivoted Column"