Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

11 REPLIES 11
wzpeli
Frequent Visitor

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

 

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

 

Regards

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

 

 

Hi @wzpeli,

 

Please share some sample data as table format for test.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

 

Hi @wzpeli,

 

I attach sample pbix file below.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft,

 

Massive thanks for your wonderful effort.

 

I have checked the result, it nearly meet my demand. The only issue is the NEXT column, machine1 and machine2 have same results, they should be individual value. Could you check it when you have free time please?

 

Many thanks in advance.

 

Regards

 

wzpeli 

 

 

 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

 

 

 

Much appreciate, I will try it right now.

 

Many thanks for your great efforts.

 

Regards

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.