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

Change time series in POWER BI

Hi Master,

 

I have a specific time serious issue currently, the original data set are:

 

Time                                              Name                           State

26-07-2018 00:00:00                    Machine A                    Start

26-07-2018 00:01:00                    Machine A                    Stop

26-07-2018 00:05:00                    Machine B                    Start

26-07-2018 00:08:00                    Machine B                    Stop

26-07-2018 00:08:30                    Machine C                    Start

26-07-2018 00:10:00                    Machine C                    Stop

26-07-2018 00:12:00                    Machine A                   Start

.....                                                 ......                                  ......

 

What I want to get the data set are:

 

Name                                   StartTime                                    StopTime                        Next Start Time    

Machine A                          26-07-2018 00:00:00                 26-07-2018 00:01:00         26-07-2018 00:05:00

Machine B                          26-07-2018 00:05:00                 26-07-2018 00:08:00          26-07-2018 00:08:30

Machine C                          26-07-2018 00:08:30                 26-07-2018 00:10:00          26-07-2018 00:12:00

Machine ...                          26-07-2018 00:12:00                 .....                                       ......

 

The challenge is to change the time series to a different format, there are around millions row like this, but I don't know how to get there.

 

Could you please help me to solve it? Massive thanks in advance.

 

Regards

 

Power BI Learner.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Change time series in POWER BI

Hi @wzpeli,

 

You can try to use below query to achieve your requirement:

10.PNG

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLTNTDXNTIwtFAwMLACIyUdJd/E5IzMvFQFRyA7uCSxqEQpVgdDrSE2tfkF2JSaoip1wmesBTa12I21sDJGVuqMx1hDNJ85w42NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t, Name = _t, State = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type text}, {"Name", type text}, {"State", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[State]), "State", "Time"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Pivoted Column", {{"Start", type datetime}, {"Stop", type datetime}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Next", each Function.Invoke((paraTb as table, dt as datetime)=>Table.SelectRows(paraTb , each [Start] > dt)[Start]{0},{#"Changed Type with Locale",[Stop]})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Next", type datetime}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Next", null}})
in
    #"Replaced Errors"

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

11 REPLIES 11
wzpeli
Frequent Visitor

Re: Change time series in POWER BI

Can this data set be done in Power BI or not?

 

If not, please do let me know, I will try other solutions.

 

Regards

 

Community Support
Community Support

Re: Change time series in POWER BI

Hi @wzpeli,

 

You can try to use below query to achieve your requirement:

10.PNG

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLTNTDXNTIwtFAwMLACIyUdJd/E5IzMvFQFRyA7uCSxqEQpVgdDrSE2tfkF2JSaoip1wmesBTa12I21sDJGVuqMx1hDNJ85w42NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t, Name = _t, State = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type text}, {"Name", type text}, {"State", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[State]), "State", "Time"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Pivoted Column", {{"Start", type datetime}, {"Stop", type datetime}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Next", each Function.Invoke((paraTb as table, dt as datetime)=>Table.SelectRows(paraTb , each [Start] > dt)[Start]{0},{#"Changed Type with Locale",[Stop]})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Next", type datetime}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Next", null}})
in
    #"Replaced Errors"

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

wzpeli
Frequent Visitor

Re: Change time series in POWER BI

Much appreciate, I will try it right now.

 

Many thanks for your great efforts.

 

Regards

 

 

wzpeli
Frequent Visitor

Re: Change time series in POWER BI

Hi Xiaoxin,

 

Massive thanks for your previous efforts, it nearly worked.

 

I have tried your method in my data tables, it has a little format issue, could you have a look please? For each machine, I want to only one row not two rows. Please find the following picture and queries, Many thanks in advance.

 

Regards

 

    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Time", type text}, {"State", type text}, {"Name", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[State]), "State", "Time"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Pivoted Column", {{"Start", type datetime}, {"Stop", type datetime}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Next", each Function.Invoke((paraTb as table, dt as datetime)=>Table.SelectRows(paraTb , each [Start] > dt)[Start]{0},{#"Changed Type with Locale",[Stop]})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Next", type datetime}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Next", null}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Errors",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Name", "Start", "Stop", "Next"})
in
    #"Reordered Columns"

 

 

 

power bi machine.PNG

 

 

 

Community Support
Community Support

Re: Change time series in POWER BI

Hi @wzpeli,

 

Please share some sample data as table format for test.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
wzpeli
Frequent Visitor

Re: Change time series in POWER BI

 Hi Xiaoxin,

 

I have tried to upload the sample data in here, but don't know how to do it, even I have changed the files format to Photos, it doesn't help.

 

Please find the following example pictures, you can just use the first 10 or 20 rows as the example to create the table. I apologise for the sample data not in your hand.

 

 

Book1 - Machine test.PNG

 

Many thanks again for your great patience, all the efforts you made are extremely well received and appreciate.

 

Regards

 

 

 

wzpeli
Frequent Visitor

Re: Change time series in POWER BI

Hi Xiaoxin,

 

Could you have a look my example when you are free please?

 

This issue is quite tricky for me, and I really appreciate if it can be solved.

 

Many thanks in advance

 

Regards

 

wzpeli

Highlighted
Community Support
Community Support

Re: Change time series in POWER BI

Hi @wzpeli,

 

Maybe you can copy some same data to excel, upload to onedrive or google drive and pasted share link here.
It is hard to troubleshoot with snapshots.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
wzpeli
Frequent Visitor

Re: Change time series in POWER BI

Hi Xiaoxin,

 

Many thanks for your useful suggestion.

 

Please find the sample data in OneDrive link:

 

https://1drv.ms/x/s!AvLDmanrMNuzhAE-CaLx_9wVHbdq

 

Much appreciate for your help.

 

Regards

 

wzpeli

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors