cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahrvanovic
Frequent Visitor

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 1Table 2Table 2

 

Thank you. 

Source table: https://printgs-my.sharepoint.com/:x:/g/personal/ahrvanovic_printgs_onmicrosoft_com/ERvNbDQhHBRKv0AN...

1 ACCEPTED SOLUTION

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"

View solution in original post

9 REPLIES 9
mah_priya94
Helper I
Helper I

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"

mah_priya94_0-1621278455297.png

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/ERvNbDQhHBRKv0AN...

Table 1

pbi doc - https://printgs-my.sharepoint.com/:u:/g/personal/ahrvanovic_printgs_onmicrosoft_com/EU_6YL2BavtHgusd...

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



Hi,

One doubt, Is it possible for another same Operation to start if the previous Operation has not stopped yet.

 

mah_priya94_0-1621318016055.png

 

I dont think so.
Same operation must start and end after. 

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"

View solution in original post

Thank you very much. 

Happy to help! 🙂

ahrvanovic
Frequent Visitor

Dear CNENFRNL, thank you for your time, but it seams i Cant get expected results. 
When i insert more rows to source table input tableinput table

 

This is result I got. 

result tableresult 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/ERvNbDQhHBRKv0AN...

CNENFRNL
Super User III
Super User III

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"

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors