Forum Discussion
Transforming Data - help needed
I need to transform data from table 1 to table 2. What is the best way to do that?
Table 1Table 2
Thank you.
Source table: https://printgs-my.sharepoint.com/:x:/g/personal/ahrvanovic_printgs_onmicrosoft_com/ERvNbDQhHBRKv0ANypLKCM0Br3hMti2CCmLP7SqyWccxJA?e=DelNK8
HI ahrvanovic ,
Please run the following query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5BDoQwCEXvwrqLQutUuYVxo9O4mBOMMd4/EkhJddE0/Jf/oFZACLDKW67feckfOUbYQwVy8D80R8vTq4AN5GcBmSwfZNi6ArUNn5fJQfGGmlLLx+eG3PJJhm8nSpwMyGUB5o5kpsEIOlFX4VwMkMsUjNrYbw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Operation = _t, Type = _t, Time = _t]),
#"Grouped Rows" = Table.Group(Source, {"Type", "Operation"}, {{"Data", each _, type table [ID=nullable text, Operation=nullable text, Type=nullable text, Time=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "order",1)),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Time", "order"}, {"Custom.Time", "Custom.order"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom1",{"Data"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Custom.order", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each [Operation]&"-"&[Custom.order]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.Time", type time}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Type]), "Type", "Custom.Time"),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Start", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Custom.order", "Custom"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "NewID"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"NewID", "Operation", "Start", "Stop"})
in
#"Reordered Columns"
9 Replies
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYoA4uCSxKISIG1gYGVgoBSrE61kBJVxzUuBiBtCxI3RdRjCZExQdRhaGQHFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Operation = _t, Type = _t, Time = _t]), Custom1 = Table.AddIndexColumn(Table.FromColumns({List.Transform(Table.Split(Source,2), each Table.Pivot(Table.RemoveColumns(_, {"ID"}), List.Distinct([Type]), "Type", "Time"))}, {"t"}), "New ID", 1), #"Expanded t" = Table.ExpandTableColumn(Custom1, "t", {"Operation", "Start", "End"}, {"Operation", "Start", "End"}) in #"Expanded t"Hi ahrvanovic ,
Please try the following :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYoA4uCSxKISIG1gZWCgFKsTrWQEl8gvAIsbQsSN0TQYwiRMUDUYWhlBxE2BnEgkDUYwG8zQTIJLmMN1gE0yholboNpgAhaPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Operation = _t, Type = _t, Time = _t]),
#"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Type]), "Type", "Time"),
#"Filled Down" = Table.FillDown(#"Pivoted Column",{"Start"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Stop] <> null and [Stop] <> ""),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "New ID", 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"ID"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"New ID", "Operation", "Start", "Stop"})
in
#"Reordered Columns"Would request you to provide a more concrete sample.
Currently, there is no other key in the table to perform this operation.https://printgs-my.sharepoint.com/:x:/g/personal/ahrvanovic_printgs_onmicrosoft_com/ERvNbDQhHBRKv0ANypLKCM0Br3hMti2CCmLP7SqyWccxJA?e=lmFbrI
Table 1
pbi doc - https://printgs-my.sharepoint.com/:u:/g/personal/ahrvanovic_printgs_onmicrosoft_com/EU_6YL2BavtHgusd_NS-DlkBAZgR7hTyqGrnZKJBUCAtRA?e=giQ6ra
Here is source table and pbi document. Can u please apply your solution in this pbi doc.
It is working with table enter manually but i cant change the source of table in this solution. Its probably posible but i dont have knowlidge for that 😞
Real case for this scenario is import table from SQL server, with new entries everyday.
That table register
Operations ID;
sequential ID - next entry = biger ID integer;
Type of entry - Stop or Start,
Time of entry;
I know there is no key for operation, but as far as I understand problem here, there should be a way to give key to every operation Start and find end time as a first next stop entry for that operation. Or there is some other aproach for solving this. Much more eficient or ...
My problem is that am really new to BI so i dont have knowlidge to do that.
So can u please use pbix and table that im sharing so i can change source of data later with no problems and try to understand what is going on.
Thank you
Dear CNENFRNL, thank you for your time, but it seams i Cant get expected results.
When i insert more rows to source table input tableThis is result I got.
result table
Can you please use this excel table as a source, because real table i need to transform is more complex than the sample source table i presented here. Thank you once more.
https://printgs-my.sharepoint.com/:x:/g/personal/ahrvanovic_printgs_onmicrosoft_com/ERvNbDQhHBRKv0ANypLKCM0Br3hMti2CCmLP7SqyWccxJA?e=DelNK8
