cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thomam Occasional Visitor
Occasional Visitor

Scripting Datetime CSV (Phyton or R Script)

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Scripting Datetime CSV (Phyton or R Script)

Hi  @thomam 

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

8.png9.png

 

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))
10.png
 
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.

1 REPLY 1
Community Support Team
Community Support Team

Re: Scripting Datetime CSV (Phyton or R Script)

Hi  @thomam 

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

8.png9.png

 

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))
10.png
 
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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 102 members 1,622 guests
Please welcome our newest community members: