Enrich data in power query

[Nikojoer]

Hi all

Every month I receive an excel file with time registration. The file is a csv file and it needs to be “cleaned” before I can use the data. Therefore am I thinking of making a power query, but I would also like to add some more info to the data – and here is where I need your guys help.

The data does not have date information and do only have accumulated numbers. I am thinking that the power query should get the data form the folder are I will place the files I extract every month. Is it possible to enrich data with a month date for the different months and also to subtract the previous months’ time registrations, so I do not have accumulated numbers?

Thanks.

Br
Nikolaj

 

[alansidman]

Show us a sample of what your data looks like now. What you want it to look like and where the additional data is coming from. Your request is to vague to offer up any kind of helpful advise without seeing what we are up against.

 

[Nikojoer]

Thanks for the reply. I have attached an example of the data I receive(AS IS image). Here I want to add the month and calculate the current month time registration (last months accumulated time minus this months accumulated time). I have tried to show an example in the TO BE image.

Can I get power query to clean the data and add the other informations? Or is there another way you will recommend
?
AS IS

I


TO BE

 

[sandy666]

Next time try to use XL2BB, nobody want to retype your data from the pictures, also could you explain your logic from source to expected result?
The rest I'll leave in @alansidman 's hands

sndtfd021
	A	B	C	D	E	F	G	H	I	J
1	Raw data
2
3	|Alias Statistik
4	Joi-01-2020- 31-01-2020
5	Der er ikke filtreret pa godkendte, og ikke godkendte timer.
6	Gruppering efter: Bruger					Expected
7	Statistik udtrukket den 30-03-2020 11:31:25
8	Name	Project	Time			Name	Date	Project	Time reg in the month	akk. Time
9	name 1	Project 1	74			name 1	January	Project 1	10	74
10	name 2	Project 1	46.5			name 2	January	Project 1	20	46.5
11	name 3	Project 1	127			name 3	January	Project 1	23	127
12	name 4	Project 1	44.4			name 4	January	Project 1	5	44.4
13	name 5	Project 1	40			name 5	January	Project 1	8	40
14	name 6	Project 1	21			name 6	January	Project 1	2	21
15	name 7	Project 1	50.4			name 7	January	Project 1	21	50.4
16	name 8	Project 1	45			name 8	January	Project 1	12	45
17	name 9	Project 1	4			name 9	January	Project 1		4
18	name 4	Project 2	16			name 4	January	Project 2	1	16
19	name 5	Project 2	23.4			name 5	January	Project 2	2	23.4
20	name 6	Project 2	21.5			name 6	January	Project 2	3	21.5
21	name 13	Project 3	5			name 13	January	Project 3	9	5
22	name 14	Project 3	50			name 14	January	Project 3	6	50
23	name 15	Project 3	31			name 15	January	Project 3	3	31
24	name 16	Project 3	10			name 16	January	Project 3	7	10
25	name 17	Project 3	14			name 17	January	Project 3	3	14
26	name 18	Project 3	12			name 18	January	Project 3	7	12
27	name 19	Project 3	12			name 19	January	Project 3	3	12
28	name 1	Project 4	74			name 1	January	Project 4	8	74
29	name 2	Project 5	8.5			name 2	January	Project 5	3	8.5
30	name 3	Project 6	6			name 3	January	Project 6	2	6
31	name 4	Project 7	294.25			name 4	January	Project 7	23	56
32		Total	5719.13					Total		5719.13
33	Roller:
34	[Admin]
35	[Bruger]
36	Rettigheder:
37	[EnhedsChef]
38	[P-leder]
Sheet1

 

[alansidman]

Still need to know where the data in Sandy's Column I comes from. We need to be able to get that data from somewhere. Supply the source of that and be sure to use XL2BB as Sandy suggested.

 

[Nikojoer]

Sorry for not using XL2BB.


The way I think I should get the information in column "I" in Sandy excel file, is by saying “December 2019” data is my “start” time. From here I will extract monthly data and then subtract last month's figures with the current month figures. So, the difference will be this month registered hours.


I have tried to show it in the excel sheet below.

Timereg. 01.2020.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Alias Statistik
2	01-01-2020 - 31-03-2020								Expeted												Folder
3	Der er ikke filtreret på godkendte, og ikke godkendte timer.									NAME	Date	PROJECT	Time reg in the month	akk. Time
4	Gruppering efter: Bruger									Name 1	dec-19	Project 1		30
5	Statistik udtrukket den 30-03-2020 11:31:25									Name 2	dec-19	Project 1		40
6	NAME	PROJECT	akk. Time							Name 2	dec-19	Project 2		50
7	Name 1	Project 1	40							Name 3	dec-19	Project 2		50
8	Name 2	Project 1	60							Name 1	dec-19	Project 3		50
9	Name 2	Project 2	60							Name 4	dec-19	Project 3		50
10	Name 3	Project 2	70							Name 5	dec-19	Project 3		40
11	Name 1	Project 3	60							Name 1	jan-20	Project 1	10	40
12	Name 4	Project 3	65							Name 2	jan-20	Project 1	20	60
13	Name 5	Project 3	50							Name 2	jan-20	Project 2	10	60
14										Name 3	jan-20	Project 2	20	70
15	Roller:									Name 1	jan-20	Project 3	10	60
16	[Admin]									Name 4	jan-20	Project 3	15	65
17	[Bruger]									Name 5	jan-20	Project 3	10	50
18	Rettigheder:									Name 1	feb-20	Project 1	3	43
19	[EnhedsChef]									Name 2	feb-20	Project 1	10	70
20	[P-leder]									Name 2	feb-20	Project 2	3	63
21	[Alle tider er i timer/hundrededele]									Name 3	feb-20	Project 2	15	85
22	v. 5.2.5.5032 30-03-2020 11:31:34									Name 1	feb-20	Project 3	3	63
23										Name 4	feb-20	Project 3	10	75
24										Name 5	feb-20	Project 3	15	65
25
26
27
Timereg. 01.2020

Cell Formulas
Range	Formula
M11:M24	M11	=+N11-N4

 

[sandy666]

I'll interfere again,
first read my green bottom line
next
my dear friend, to do something you'll need data from previous month(s) also but you posted example from January (source data) only
forget for a moment about expected result but do what you want with source data you posted (and only with this data!). Hope you understand me ?
in short: no data? no solution, no result!

if you've excel file with all data you can share this file via dropbox, onedrive or any similar and post link to this file here

@alansidman : I apologize for interfering