Need to Pull from CSV rows into new sheet columns

[edjohn20]

I have attached the images sample 1 and sample 2. My data comes to me via sample 1 in row form however I have to import data via format sample 2. I only need the USD lines and cash dep lines are ignored.

you can see location 609b gets 20's and 50's but it needs to be moved to this type of formatting

and though the LOC ids aren't the same (i just used two different files) you can see how I need all the data for one LOC on one line.

Does anyone have an easy way to do this. I am having to manually create this sheet daily and it is killing time.

 

[Kerryx]

One of the people with more experience in writing the code directly may be able to tidy it up, but if it works , go with it.

 

[edjohn20]

Ok decided to give a head start is it something like this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Company_Name	Filter_Name	Request ID	Print Date	Transport	Delivery Date	AU_Number	Route	Local#(If)	Local#(If)_1	ATM ID	Currency	Denomination	Current_Load	Fill_Method
2	A	Filt	1	01/02/2024	Truck	04/02/2024	123	1	1	1	0609B	USD	Twenties	60000	Manual
3	A	Filt	1	02/02/2024	Truck	04/02/2024	123	1	1	1	0609B	USD	Fifties	100000	Manual
4	A	Filt	1	03/02/2024	Truck	04/02/2024	123	1	1	1	0609B	Cash Dep	Cash Dep	0	Manual
5	B	Filt	2	04/02/2024	Truck	04/02/2024	456	2	2	2	0816C	USD	Twenties	70000	Manual
6	B	Filt	2	05/02/2024	Truck	04/02/2024	456	2	2	2	0816C	USD	Fifties	100000	Manual
7	B	Filt	2	06/02/2024	Truck	04/02/2024	456	2	2	2	0816C	Cash Dep	Cash Dep	0	Manual
8	C	Filt	3	07/02/2024	Truck	04/02/2024	789	3	3	3	1242A	USD	Fifties	140000	Manual
9	C	Filt	3	08/02/2024	Truck	04/02/2024	789	3	3	3	1242A	USD	Twenties	160000	Manual
10	C	Filt	3	09/02/2024	Truck	04/02/2024	789	3	3	3	1242A	Cash Dep	Cash Dep	0	Manual
11	D	Filt	4	10/02/2024	Truck	04/02/2024	741	4	4	4	1242H	USD	Twenties	50000	Manual
12	D	Filt	4	11/02/2024	Truck	04/02/2024	741	4	4	4	1242H	USD	Fifties	100000	Manual
13	D	Filt	4	12/02/2024	Truck	04/02/2024	741	4	4	4	1242H	Cash Dep	Cash Dep	0	Manual
14	E	Filt	5	13/02/2024	Truck	05/02/2024	852	5	5	5	1670K	USD	Twenties	60000	Auto
15	F	Filt	6	14/02/2024	Truck	05/02/2024	852	6	6	6	0609B	Cash Dep	Cash Dep	0	Auto
16	B	Filt	7	15/02/2024	Truck	05/02/2024	852	7	7	7	0816C	USD	Fives	5	Auto
17	D	Filt	8	16/02/2024	Truck	05/02/2024	741	8	8	8	1242H	USD	Ones	1	Auto
18	F	Filt	9	17/02/2024	Truck	05/02/2024	963	9	9	9	1670K	USD	Hundreds	100	Auto
19	G	Filt	10	18/02/2024	Truck	05/02/2024	753	10	10	10	1456G	USD	Thousands	1000	Auto
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Count.Company_Name	Count.Filter_Name	Count.Request ID	Count.Print Date	Count.Transport	Count.Delivery Date	Count.AU_Number	Count.Route	Count.Local#(If)	Count.Local#(If)_1	Count.ATM ID	Count.Fill_Method	Twenties	Fifties	Fives	Ones	Hundreds	Thousands
2	A	Filt	1	01/02/2024	Truck	04/02/2024	123	1	1	1	0609B	Manual	60000
3	A	Filt	1	02/02/2024	Truck	04/02/2024	123	1	1	1	0609B	Manual		100000
4	B	Filt	2	04/02/2024	Truck	04/02/2024	456	2	2	2	0816C	Manual	70000
5	B	Filt	2	05/02/2024	Truck	04/02/2024	456	2	2	2	0816C	Manual		100000
6	B	Filt	7	15/02/2024	Truck	05/02/2024	852	7	7	7	0816C	Auto			5
7	C	Filt	3	07/02/2024	Truck	04/02/2024	789	3	3	3	1242A	Manual		140000
8	C	Filt	3	08/02/2024	Truck	04/02/2024	789	3	3	3	1242A	Manual	160000
9	D	Filt	4	10/02/2024	Truck	04/02/2024	741	4	4	4	1242H	Manual	50000
10	D	Filt	4	11/02/2024	Truck	04/02/2024	741	4	4	4	1242H	Manual		100000
11	D	Filt	8	16/02/2024	Truck	05/02/2024	741	8	8	8	1242H	Auto				1
12	E	Filt	5	13/02/2024	Truck	05/02/2024	852	5	5	5	1670K	Auto	60000
13	F	Filt	9	17/02/2024	Truck	05/02/2024	963	9	9	9	1670K	Auto					100
14	G	Filt	10	18/02/2024	Truck	05/02/2024	753	10	10	10	1456G	Auto						1000
Table1 (2)

As you can see in the line with Expanded Count you select all the columns you want to show so i removed Currency as you said you don't need it and Denomination is what we are grouping by so you can take that out also to prevent duplication

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company_Name", type text}, {"Filter_Name", type text}, {"Request ID", Int64.Type}, {"Print Date", type date}, {"Transport", type text}, {"Delivery Date", type date}, {"AU_Number", Int64.Type}, {"Route", Int64.Type}, {"Local#(#)(If)", Int64.Type}, {"Local#(#)(If)_1", Int64.Type}, {"ATM ID", type text}, {"Currency", type text}, {"Denomination", type text}, {"Current_Load", Int64.Type}, {"Fill_Method", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Currency] = "USD")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Denomination"}, {{"Count", each _, type table [Company_Name=nullable text, Filter_Name=nullable text, Request ID=nullable number, Print Date=nullable date, Transport=nullable text, Delivery Date=nullable date, AU_Number=nullable number, Route=nullable number, #"Local#(#)(If)"=nullable number, #"Local#(#)(If)_1"=nullable number, ATM ID=nullable text, Currency=nullable text, Denomination=nullable text, Current_Load=nullable number, Fill_Method=nullable text]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Company_Name", "Filter_Name", "Request ID", "Print Date", "Transport", "Delivery Date", "AU_Number", "Route", "Local#(#)(If)", "Local#(#)(If)_1", "ATM ID", "Current_Load", "Fill_Method"}, {"Count.Company_Name", "Count.Filter_Name", "Count.Request ID", "Count.Print Date", "Count.Transport", "Count.Delivery Date", "Count.AU_Number", "Count.Route", "Count.Local#(#)(If)", "Count.Local#(#)(If)_1", "Count.ATM ID", "Count.Current_Load", "Count.Fill_Method"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Denomination]), "Denomination", "Count.Current_Load")
in
    #"Pivoted Column"

I did have a type mismatch on route, local1 and local 2 they are text but then passed as numbers resulting in errors, but I fixed that

 

[Kerryx]

See you're picking it up already

 

[edjohn20]

See you're picking it up already

Thank you again for your help. I am now going to try and make a module that can be used outside of excel so it can be a simple couple of clicks and a new file is produced.

 

[edjohn20]

See you're picking it up already

Okay so I took what you did and what little I learned on my own and tried to do the same thing with a different file and layout. Would you be willing to review this setup to see where I went wrong? I feel like I am so close...