Help with adding data

[ThayCB]

Good afternoon. I'm trying to perform a procedure in Power BI and I would like to know if someone could help me.

I have an Excel spreadsheet with only one column where each row contains one second of the day, from 00:00:00 to 23:59:00:

00:00:00
00:00:01
00:00:02
00:00:03
00:00:04
00:00:05...

I have another file where I want to join this one, in it I have some data in specific seconds of the day:

A|00:00:00
B|00:00:02
C|00:00:03
D|00:00:05...

How can I get this data at specific times and put it next to the corresponding time of day from the first worksheet? I would like that when including the data, it would look like this:

00:00:00|A|00:00:00
00:00:01|  |
00:00:02|B|00:00:02
00:00:03|C|00:00:03
00:00:04|  |
00:00:05|D|00:00:05...

Thanks!

[ThayCB]

RESULTS TABLE 1:
DATA	HOUR	CLIENT A - 111	CUSTOMER B - 222	CLIENT B - 223	CLIENT C - 333	CLIENT D - 444
03/29/2022	00:00:00	NOT FOUND	NOT FOUND	104	NOT FOUND	101
03/29/2022	00:00:01	NOT FOUND	101	104	NOT FOUND	101
03/29/2022	00:00:02	101	101	104	NOT FOUND	101
03/29/2022	00:00:03	101	101	104	NOT FOUND	101
03/29/2022	00:00:04	102	NOT FOUND	104	NOT FOUND	102
03/29/2022	00:00:05	102	105	NOT FOUND	NOT FOUND	102
03/29/2022	00:00:06	102	NOT FOUND	NOT FOUND	NOT FOUND	102
03/29/2022	00:00:07	103	103	NOT FOUND	NOT FOUND	102
03/29/2022	00:00:08	103	103	NOT FOUND	NOT FOUND	101
03/29/2022	00:00:09	103	103	NOT FOUND	NOT FOUND	101
03/29/2022	00:00:10	NOT FOUND	103	NOT FOUND	NOT FOUND	101
...	...	...	...	...	...	...

RESULTS TABLE 2:
DATA	HOUR	CLIENT A - 111	CUSTOMER B - 222	CLIENT B - 223	CLIENT C - 333	CLIENT D - 444
03/30/2022	00:00:00	NOT FOUND	NOT FOUND	104	101	NOT FOUND
03/30/2022	00:00:01	NOT FOUND	NOT FOUND	104	104	NOT FOUND
03/30/2022	00:00:02	NOT FOUND	NOT FOUND	104	108	NOT FOUND
03/30/2022	00:00:03	NOT FOUND	NOT FOUND	104	108	NOT FOUND
03/30/2022	00:00:04	NOT FOUND	NOT FOUND	104	605	NOT FOUND
03/30/2022	00:00:05	105	NOT FOUND	NOT FOUND	605	NOT FOUND
03/30/2022	00:00:06	NOT FOUND	NOT FOUND	NOT FOUND	108	NOT FOUND
03/30/2022	00:00:07	NOT FOUND	NOT FOUND	NOT FOUND	108	NOT FOUND
03/30/2022	00:00:08	NOT FOUND	103	NOT FOUND	605	NOT FOUND
03/30/2022	00:00:09	NOT FOUND	103	NOT FOUND	605	NOT FOUND
03/30/2022	00:00:10	NOT FOUND	NOT FOUND	NOT FOUND	108	NOT FOUND
...	...	...	...	...	...	...

RESULTS TABLE 3:
DATA	HOUR	CLIENT A - 111	CUSTOMER B - 222	CLIENT B - 223	CLIENT C - 333	CLIENT D - 444
03/31/2022	00:00:00	NOT FOUND	NOT FOUND	NOT FOUND	NOT FOUND	777
03/31/2022	00:00:01	NOT FOUND	NOT FOUND	NOT FOUND	NOT FOUND	777
03/31/2022	00:00:02	NOT FOUND	NOT FOUND	NOT FOUND	NOT FOUND	777
03/31/2022	00:00:03	NOT FOUND	NOT FOUND	NOT FOUND	NOT FOUND	777
03/31/2022	00:00:04	NOT FOUND	NOT FOUND	NOT FOUND	NOT FOUND	777
03/31/2022	00:00:05	NOT FOUND	NOT FOUND	NOT FOUND	NOT FOUND	777
03/31/2022	00:00:06	NOT FOUND	NOT FOUND	NOT FOUND	NOT FOUND	777
03/31/2022	00:00:07	NOT FOUND	NOT FOUND	NOT FOUND	NOT FOUND	777
03/31/2022	00:00:08	NOT FOUND	NOT FOUND	NOT FOUND	NOT FOUND	777
03/31/2022	00:00:09	NOT FOUND	NOT FOUND	NOT FOUND	NOT FOUND	777
03/31/2022	00:00:10	NOT FOUND	NOT FOUND	NOT FOUND	NOT FOUND	777
...	...	...	...	...	...	...

That's what I'm trying to do, any help will help, ty 😃

[ThayCB]

DATA TABLE 1:
CLIENT	EQUIPMENT	DATE	START TIME	DURATION (SEC)	SECTION
THE	111	03/29/2022	00:00:02	two	101
THE	111	03/29/2022	00:00:04	3	102
THE	111	03/29/2022	00:00:07	4	103
B	222	03/29/2022	00:00:01	3	101
B	222	03/29/2022	00:00:07	1	105
B	222	03/30/2022	00:00:08	two	101
B	223	03/30/2022	00:00:00	5	104
D	444	03/29/2022	00:00:00	4	101
D	444	03/29/2022	00:00:04	4	102
D	444	03/29/2022	00:00:08	5	101
...	...	...	...	...	...

DATA TABLE 2:
CLIENT	EQUIPMENT	DATE	START TIME	DURATION (SEC)	SECTION
THE	111	03/29/2022	00:00:05	1	105
D	444	03/31/2022	00:00:00	1320	777
C	333	03/30/2022	00:00:00	1	101
C	333	03/30/2022	00:00:01	1	104
C	333	03/30/2022	00:00:02	two	108
C	333	03/30/2022	00:00:04	two	605
C	333	03/30/2022	00:00:06	two	108
C	333	03/30/2022	00:00:08	two	605
C	333	03/30/2022	00:00:10	208	108
...	...	...	...	...	...

[ThayCB]

Ok, I'm trying something a bit complicated for myself yet, but what I need is:

- I have 1 Source Table each day, within that, I have some information of today, tomorrow and yesterday, showing me the start time a client had started a task.

- I need to separate the information per day and distribute second per second.

Here the images, below, the tables. I don't need the colors, it's just for reference.

[v-yalanwu-msft]

Hi, @ThayCB ;

Regardless of whether you can achieve the results you want, we can first see whether it can be achieved logically. For example, let's make an analogy: if in hour_ The 00:00:03 one to many in the 0 table corresponds to hour_ 1 multiple values in the table, then if I want to be in hour_ Which value is returned in the 0 table?

so you could provide more  more detailed logic or show it with a simple example and the results you want to output?

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

[ThayCB]

It's giving me some errors:

I tried "both" instead of single, same error:

When I try the opposite table, it works, ask me to select the table and everything:

But I need the opposite, include the Hour_2 int Hour_0 table. If possible, the 4 columns of Hour_2 distributed next to Hour_0.

Obs.: My Hour_0 column have values from 00:00:00 to 00:30:00. And I know that the table Hour_0 and his column is also named Hour_0, but it's not the problem.

Thanks for now 😃

[Samarth_18]

Hi @ThayCB ,

Please create a relationship with the seconds in the table then with the below code create a new column in 2nd table:-

column = related(seconds_column)

Thanks,

Samarth

[nvprasad]

Hi ThayCB,

Thnking about, Crating a new column in the second table within the power query editor with only a seconds part(without text). Then we can join both the column in modeling and get the required output column.

Can you try and let us know?

Thank You,

Regards,

N V Durga Prasad