Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi @wzpeli,
You can try to use below query to achieve your requirement:
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
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:
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
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"
Hi @wzpeli,
Please share some sample data as table format for test.
Regards,
Xiaoxin Sheng
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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |