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

[ErnestoAG]

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.

[Ashish_Mathur]

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?

[ErnestoAG]

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.

[Ashish_Mathur · Accepted solution]

Hi,

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

Hope this helps.

[ErnestoAG]

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.

---