Missy Record from Power Query

[jarett]

I have a file folder where I am pulling the value from the same cell in each file, then recording it in the next line/row with the file name on a separate worksheet. I had implemented a filters but can't seem to find why one files value is getting left off (10_5_2023_2), I have another file (09_15_2023_4) with the same value in that cell and it is include in my output file. I tried deleting filtered rows, but can't find which one is affecting that record. The last file in this screenshot is the file I am losing somewhere.

This is my file where I am storing the values from each file in a specified folder.

Inventory_Average.xlsx
	A	B	C	D
1	Name	Sheets.Data.Column3	Inventory Discrepance % Average	Goal = 0%
2	09_01_2023_1.xlsx	25%	22.01%	Excellent = 0.9%-5%
3	09_01_2023_9.xlsx	9%		Good = 5.1%-15%
4	09_06_2023_1.xlsx	17%		Average=15.1%-25%
5	09_06_2023_2.xlsx	6%		Needs Attention > 25.1%
6	09_06_2023_3.xlsx	18%
7	09_07_2023_1.xlsx	16%
8	09_07_2023_2.xlsx	15%
9	09_07_2023_3.xlsx	5%
10	09_08_2023_1.xlsx	9%
11	09_08_2023_2.xlsx	13%
12	09_08_2023_3.xlsx	10%
13	09_08_2023_4.xlsx	34%
14	09_08_2023_5.xlsx	60%
15	09_11_2023_1.xlsx	30%
16	09_12_2023_1.xlsx	19%
17	09_13_2023_1.xlsx	11%
18	09_13_2023_2.xlsx	12%
19	09_15_2023_1.xlsx	13%
20	09_15_2023_13.xlsx	1%
21	09_15_2023_14.xlsx	39%
22	09_15_2023_3.xlsx	1%
23	09_15_2023_4.xlsx	0%
24	09_15_2023_5.xlsx	57%
25	09_15_2023_8.xlsx	2%
26	09_15_2023_9.xlsx	2%
27	09_18_2023_1.xlsx	23%
28	09_18_2023_10.xlsx	54%
29	09_18_2023_11.xlsx	41%
30	09_18_2023_12.xlsx	5%
31	09_18_2023_13.xlsx	50%
32	09_18_2023_14.xlsx	35%
33	09_18_2023_15.xlsx	9%
34	09_18_2023_16.xlsx	2%
35	09_18_2023_17.xlsx	84%
36	09_18_2023_18.xlsx	50%
37	09_18_2023_2.xlsx	1%
38	09_18_2023_21.xlsx	33%
39	09_18_2023_23.xlsx	58%
40	09_18_2023_24.xlsx	1%
41	09_18_2023_27.xlsx	16%
42	09_18_2023_28.xlsx	31%
43	09_18_2023_29.xlsx	25%
44	09_18_2023_3.xlsx	17%
45	09_18_2023_30.xlsx	55%
46	09_18_2023_31.xlsx	22%
47	09_18_2023_32.xlsx	3%
48	09_18_2023_4.xlsx	17%
49	09_18_2023_5.xlsx	50%
50	09_18_2023_6.xlsx	30%
51	09_18_2023_8.xlsx	25%
52	09_19_2023_1.xlsx	63%
53	09_20_2023_1.xlsx	4%
54	09_20_2023_3.xlsx	6%
55	09_20_2023_4.xlsx	75%
56	09_20_2023_5.xlsx	10%
57	09_20_2023_6.xlsx	1%
58	09_20_2023_7.xlsx	8%
59	09_21_2023_1.xlsx	55%
60	09_21_2023_11.xlsx	20%
61	09_21_2023_12.xlsx	48%
62	09_21_2023_2.xlsx	17%
63	09_21_2023_3.xlsx	2%
64	09_21_2023_4.xlsx	64%
65	09_21_2023_6.xlsx	21%
66	09_21_2023_7.xlsx	12%
67	09_21_2023_8.xlsx	3%
68	09_21_2023_9.xlsx	23%
69	09_22_2023_1.xlsx	8%
70	09_25_2023_3.xlsx	22%
71	09_25_2023_4.xlsx	4%
72	09_25_2023_5.xlsx	19%
73	09_25_2023_6.xlsx	3%
74	09_25_2023_7.xlsx	19%
75	09_25_2023_8.xlsx	2%
76	09_26_2023_1.xlsx	3%
77	09_26_2023_2.xlsx	30%
78	09_26_2023_3.xlsx	23%
79	09_26_2023_5.xlsx	22%
80	09_26_2023_99.xlsx	9%
81	09_27_2023_2.xlsx	29%
82	09_27_2023_3.xlsx	23%
83	09_27_2023_4.xlsx	13%
84	09_27_2023_5.xlsx	19%
85	09_28_2023_1.xlsx	19%
86	09_29_2023_1.xlsx	17%
87	09_29_2023_2.xlsx	21%
88	09_29_2023_3.xlsx	15%
89	10_02_2023_1.xlsx	18%
90	10_03_2023_1.xlsx	9%
91	10_03_2023_2.xlsx	18%
92	10_05_2023_1.xlsx	54%
93	10_05_2023_12.xlsx	16%
Inventory_Discrepance

Cell Formulas
Range	Formula
C2	C2	=AVERAGEIF(B2:B5090,">0")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	Cell Value	between 0.151 and 0.25	text	NO
C2	Cell Value	>0.251	text	NO
C2	Cell Value	between 0.051 and 0.15	text	NO
C2	Cell Value	between 0.001 and 0.049	text	NO
C2	Cell Value	=0	text	NO

These are my steps in Power Query

let
    Source = Folder.Files("L:\Warehouse and Gen Inventory\Inventory_Discrepance"),
    #"Filtered Rows2" = Table.SelectRows(Source, each not Text.StartsWith([Name], "07")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Sheets", each Excel.Workbook(File.Contents([Folder Path]&[Name]))),
    #"Expanded Sheets" = Table.ExpandTableColumn(#"Added Custom", "Sheets", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Sheets.Name", "Sheets.Data", "Sheets.Item", "Sheets.Kind", "Sheets.Hidden"}),
    #"Expanded Sheets.Data" = Table.ExpandTableColumn(#"Expanded Sheets", "Sheets.Data", {"Column3"}, {"Sheets.Data.Column3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Sheets.Data",{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Sheets.Item", "Sheets.Kind", "Sheets.Hidden", "Sheets.Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each true),
    #"Removed Blank Rows" = Table.SelectRows(#"Filtered Rows", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Sheets.Data.Column3", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each Text.StartsWith([Sheets.Data.Column3], "0.")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Sheets.Data.Column3", Percentage.Type}})
in
    #"Changed Type1"

 

[RoryA]

You have the data and the query, so at which step does the information disappear from the results?

 

[jarett]

You have the data and the query, so at which step does the information disappear from the results?

I figured out it is this step with the filter that is causing the issue,

#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each Text.StartsWith([Sheets.Data.Column3], "0.")),

The step before has the file names listed a bunch of times and the only rows that I want to include contain a number > 0

 

[RoryA]

If it disappears at that point, then whatever row you are expecting to be there does not match your criterion.

 

[jarett]

If it disappears at that point, then whatever row you are expecting to be there does not match your criterion.

After a little trial and error, I got it to include that record. Had to add some more filters and edit one that was already there.

 #"Filtered Rows3" = Table.SelectRows(#"Changed Type", each [Sheets.Data.Column3] <> "000" or [Sheets.Data.Column3] <> "001" and [Sheets.Data.Column3] <> "002" and [Sheets.Data.Column3] <> "003" and [Sheets.Data.Column3] <> "WarehouseCode"),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows3", each Text.StartsWith([Sheets.Data.Column3], "0")),
    #"Filtered Rows4" = Table.SelectRows(#"Filtered Rows1", each [Sheets.Data.Column3] <> "000" and [Sheets.Data.Column3] <> "001" and [Sheets.Data.Column3] <> "002" and [Sheets.Data.Column3] <> "003" and [Sheets.Data.Column3] <> "009"),