Formula is changing after getting new data?

[JamesRo]

I have the following sheet which looks at another sheet to calculate product usage.

sss.xlsx
	F	G	H	I	J	K	L	M	N
1	Mon 26 Jun 2023 AM	Mon 26 Jun 2023 PM	Tue 27 Jun 2023 AM	Tue 27 Jun 2023 PM	Wed 28 Jun 2023 AM
2	0	0	0	0	0
3	-3	0	-8	0	-7
4	-2	0	0	0	0
5	0	0	-5	0	0
6	0	0	0	0	0
7	-7	0	0	0	-3
8	0	0	0	0	0
9	-1	0	0	0	0
10	0	0	0	0	0
11	0	0	0	0	0
12	-1	0	-4	0	0
13	0	0	0	0	0
14	-1	0	-1	0	-1
Usage

Cell Formulas
Range	Formula
F1:J1	F1	=IF(Daily_Stock!F$1="","",VLOOKUP($A$1,Daily_Stock!$A$1:$BU$40,COLUMN(),FALSE))
K1:L1	K1	=IF(Daily_Stock!L$1="","",VLOOKUP($A$1,Daily_Stock!$A$1:$BU$40,COLUMN(),FALSE))
F2:J14	F2	=IF(Daily_Stock!F$1="","",(IFERROR(VLOOKUP($A2,Daily_Stock!$A$2:$BU$40,COLUMN(),FALSE)-VLOOKUP($A2,Daily_Stock!$A$2:$BU$40,COLUMN()-1,FALSE),"")))
K2:L14	K2	=IF(Daily_Stock!L$1="","",(IFERROR(VLOOKUP($A2,Daily_Stock!$A$2:$BU$40,COLUMN(),FALSE)-VLOOKUP($A2,Daily_Stock!$A$2:$BU$40,COLUMN()-1,FALSE),"")))

Named Ranges
Name	Refers To	Cells
Daily_Stock!ExternalData_1	=Daily_Stock!$A$1:$K$32	F1:L1

The lookup is based on this table:

sss.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Product	Wed 21 Jun 2023 AM	Thu 22 Jun 2023 PM	Fri 23 Jun 2023 AM	Fri 23 Jun 2023 PM	Mon 26 Jun 2023 AM	Mon 26 Jun 2023 PM	Tue 27 Jun 2023 AM	Tue 27 Jun 2023 PM	Wed 28 Jun 2023 AM	Wed 28 Jun 2023 PM
2	10mm Bronze	19	19	19	19	19	19	19	19	19	19
3	10mm Float	4	23	18	18	15	15	7	7
4	10mm Grey	33	33	33	33	31	31	31	31	31	31
5	10mm Opti	22	21	21	21	21	21	16	16	16	16
6	10mm Satin	25	25	25	25	25	25	25	25	25	25
7	12mm Float	10	10	10	10	3	3	3	3
8	12mm Opti Satin	4	4	4	4	4	4	4	4	4	4
9	15mm Float	11	11	11	11	10	10	10	10	10	10
10	15mm Opti	7	7	7	7	7	7	7	7	7	7
11	4mm Bronze Silv	25	25	25	25	25	25	25	25	25	25
12	4mm Float	68	66	66	66	65	65	61	61	61	61
13	4mm Opti	14	14	10	10	10	10	10	10	10	10
14	4mm Silver	32	29	28	28	27	27	26	26	25	25
Daily_Stock

This table is generated by getting data from folder of .CSV files, two a day, AM and PM.
The data is simple: product,qty.

What is happening is kind of difficult to describe.
Each time a new .csv is generated the sheets are updated but the formula in the unpopulated columns i.e. column K onwards of Usage sheet gets its formulae changed.
The column letter, e.g. in column K "=IF(Daily_Stock!L$1" has been increased by one along with every other column to the right.
I have tested, re-corrected the formula (should be K in column K and so on), and then added new .csv but the formula changes, cannot work out why...

Any help appreciated.

 

[Anthony47]

I guess that the import process start by inserting a new column in Daily_Stock, then fill this new column with its data.
Can you show the code used for that process?

 

[JamesRo]

I have just used Power Query to import the data from a folder: = Folder.Files("\\SERVER\Company Shared Files\Documents\Daily_Stock")
It takes everything that is there...

 

[Anthony47]

I am very sorry, but PowerQuery is out of my knowledge.
Can you check if the script adds a new column before copying the data?

 

[JamesRo]

I have this query "Sample File", generated when you "Get Data - From File - From Folder"

Source: = Folder.Files("\\SERVER\Company Shared Files\Documents\Daily_Stock")
Navigation: = Source{0}[Content]
Imported CSV: = Csv.Document(Navigation1,[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])
Changed Type: = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", Int64.Type}})

function = (Parameter1 as binary) => let

and Transform Sample File: = Csv.Document(Parameter1,[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])


The Daily_Stock query has the following step "Invoke Custom Function 1": = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content]))

Something here of relevance?

If it is something to do with a new .csv being added each time I might try appending the data into a single file somehow...

 

[JamesRo]

Found the editor:

let
Source = Folder.Files("\\SERVER\Company Shared Files\Documents\Daily_Stock"),
#"Sorted Rows" = Table.Sort(Source,{{"Date created", Order.Ascending}, {"Date accessed", Order.Ascending}}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Sorted Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", Int64.Type}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Source.Name", each Text.BeforeDelimiter(_, ".txt"), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Column1", "Product"}, {"Column2", "Qty"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Source.Name]), "Source.Name", "Qty", List.Sum)
in
#"Pivoted Column"

 

[Anthony47]

If no one come for a suggestion then you should republish the question under the PowerTools section, and asking how avoiding that a new column is added to the existing table before importing the new data.
Sorry...

 

[JamesRo]

Thanks. Can see why now at least...