Scripting Datetime CSV (Phyton or R Script)

[thomam]

Hello,

i'm new to Power Bi and scripting. The Script Language doesn't matter.

I want to create new Tables from one CSV.

The Data in the Csv are like this:

01:22:33;Mode;Automatic

01:22:34;State;Running

01:32:34;State;Stopped

01:32:34;Mode;Error

01:52:33;Mode;Automatic

Now i want 2 Tables: Table Mode and Table State

Table Mode like: Start;End;Value

01:22:33;01:32:34;Automatic

01:32:34;01:52:33;Error

01:52:33;NULL(or DateTime.now);Automatic

Table State like: Start;End;Value 

01:22:34;01:32:34;Running

01:32:34;NULL(or DateTime.now);Stopped

Can anyone help me with the script, is it possible to do this?

thx Thomas

[v-juanli-msft · Accepted solution]

Hi  @thomam 

In Power BI Desktop, Get data->Csv/Text

Then create two tables by Moding->New table

mode table = SELECTCOLUMNS(FILTER(new,[Column2]="Mode"),"start",[Column1],"value",[Column3])

In "mode table", create columns

index = RANKX('mode table',[start],,ASC)

end = CALCULATE(MAX('mode table'[start]),FILTER('mode table',[index]=EARLIER('mode table'[index])+1))

new table

state table = SELECTCOLUMNS(FILTER(new,[Column2]="State"),"start",[Column1],"value",[Column3])

Create columns in this table

index = RANKX('state table',[start],,ASC)

end = CALCULATE(MAX('state table'[start]),FILTER('state table',[index]=EARLIER('state table'[index])+1))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.