Reply
Frequent Visitor
Posts: 3
Registered: ‎08-10-2018
Accepted Solution

Slpit the Data into this format: 4 colums and 7 rows

Hi, I'm new in Power BI. I'm working with a software that give me a report in the following format:

 

Monday:
12:00 AM, 2;
6:30 AM, 1;
10:30 PM, 2;
Tuesday:
12:00 AM, 2;
6:30 AM, 1;
10:30 PM, 2;
Wednesday:
12:00 AM, 2;
6:30 AM, 1;
10:30 PM, 2;
Thursday:
12:00 AM, 2;
6:30 AM, 1;
10:30 PM, 2;
Friday:
12:00 AM, 2;
6:30 AM, 1;
10:30 PM, 2;
Saturday:
12:00 AM, 2;
7:30 AM, 1;
8:30 PM, 2;
Sunday:
12:00 AM, 2;
7:30 AM, 1;
8:30 PM, 2;

 

I trying to Slpit the data into this format:  4 colums and 7 rows:

 

Day           Stage 1        Stage 2      Stage 3

Monday    12:00 AM    6:30 AM    10:30 PM

Tuesday    12:00 AM    6:30 AM    10:30 PM

....

Sunday    12:00 AM     7:30 AM    8:30 PM

 

 

Where Stage 1 = 1; Stage 2 = 2;  Stage 3 = 1;  see data below

 

Monday:
12:00 AM, 2;
6:30 AM, 1;
10:30 PM, 2;

 

Any idea how I can slpitthis properly on PBI?

 

Thanks in advance.


Accepted Solutions
Super User
Posts: 3,941
Registered: ‎01-14-2017

Re: Slpit the Data into this format: 4 colums and 7 rows

Hi,

 

Here is the result i got.  You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

View solution in original post


All Replies
Super User
Posts: 3,941
Registered: ‎01-14-2017

Re: Slpit the Data into this format: 4 colums and 7 rows

Hi,

 

If 1 represents 2 stages, they how can you tell which is stage 1 and which is stage 3?  Alternatively, can it be assumed that for every day, the time stamp will always be in the order of Stage 1, Stage 2 and then Stage 3.  If my statement is correct, then what is the use of 1,2?

Frequent Visitor
Posts: 3
Registered: ‎08-10-2018

Re: Slpit the Data into this format: 4 colums and 7 rows

Hello thanks for the answer.

 

The software made the reports in that way. I thought always start by Stage 1, Stage 2 ... Stage N, where N should be the last one.

 

The data in this case:

Stage 1 = 1;  means ON

Stage 2 = 2;  means OFF

Stage 3 = 1;  means ON

 

Not always is a binary value (ON/OFF) because can have Pre-ON, Pre-OFF, Stand-By etc., however in most of the case I'm using just binary (values 1 and 2).

 

The data came as *CSV and when I import to Power BI I had all the data in one column. I know that always after a Day is a ";" and also after every Stage Value is another ";" and the time has ","

I tried first clean the data but I'm having issues to split properly, remeber all data came in one collum as below:

 

Monday:
12:00 AM, 2;
6:30 AM, 1;
10:30 PM, 2;
Tuesday:
12:00 AM, 2;
6:30 AM, 1;
10:30 PM, 2;
Wednesday:
12:00 AM, 2;
6:30 AM, 1;
10:30 PM, 2;
Thursday:
12:00 AM, 2;
6:30 AM, 1;
10:30 PM, 2;
Friday:
12:00 AM, 2;
6:30 AM, 1;
10:30 PM, 2;
Saturday:
12:00 AM, 2;
7:30 AM, 1;
8:30 PM, 2;
Sunday:
12:00 AM, 2;
7:30 AM, 1;
8:30 PM, 2;

 

Thanks again for your comments.

Super User
Posts: 3,941
Registered: ‎01-14-2017

Re: Slpit the Data into this format: 4 colums and 7 rows

Hi,

 

Here is the result i got.  You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

Frequent Visitor
Posts: 3
Registered: ‎08-10-2018

Re: Slpit the Data into this format: 4 colums and 7 rows

Thanks so much. That's the right solution.


@Ashish_Mathur wrote:

Hi,

 

Here is the result i got.  You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png