cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ErnestoAG Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

4 REPLIES 4
Super User
Super User

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?

ErnestoAG Frequent Visitor
Frequent Visitor

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
Super User

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

ErnestoAG Frequent Visitor
Frequent Visitor

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


 

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 370 members 3,857 guests
Please welcome our newest community members: