Power-Query/FolderToTable.pq at master · fossabot/Power-Query · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
(
	FolderName as text,
	optional FileName as text,
	optional SheetorTableName as any,
	optional PreFilter as function,
	optional PostFilter as function,
	optional Fold as logical) =>

let
	Source =
		try
			Folder.Files(FolderName)
		otherwise
			Folder.Files(Table.FirstValue(Excel.CurrentWorkbook(){[Name=FolderName]}[Content])),

	#"Rename File Name" = Table.RenameColumns(Source,{
			{"Name", "File Name"},
			{"Date created", "Import Date"}}),

	FilterOpenFiles = Table.SelectRows(#"Rename File Name", each
		(if FileName <> null then
			Text.Contains([File Name], FileName, Comparer.OrdinalIgnoreCase)
		else
			true)
		and
			not Text.Contains([File Name], "~$")
		and
			[Extension] <> ".tmp"
			and
				([Attributes][Kind] = "CSV File"
				or [Attributes][Kind] = "Text File"
				or [Attributes][Kind] = "Excel File")),

	Sort = Table.Sort(FilterOpenFiles ,{{"Import Date", Order.Descending}}),

	PreFiltered = Table.Buffer(
		if PreFilter<> null then
			PreFilter(Sort)
		else
			Table.FirstN(Sort,1)),

	OpenWorkBooks = Table.AddColumn(PreFiltered, "WorkBook", each
		if [Attributes][Kind] = "Excel File" then
			Excel.Workbook([Content])
		else
			null
	, type table),

	OpenCSV = Table.AddColumn(OpenWorkBooks, "CSV", each
		if [Attributes][Kind] = "CSV File" or [Attributes][Kind] = "Text File" then
			try
				Csv.Document([Content])
			otherwise
				null
		else
			null
	, type table),

	#"Expanded WorkBook" = Table.ExpandTableColumn(OpenCSV, "WorkBook",
		{"Name", "Data", "Item", "Kind", "Hidden"},
		{"Sheet Name", "Data", "Item", "Kind", "Hidden"}),

	#"Kept Range of Rows" =
		try
			if Value.Is(SheetorTableName, type number) then
				Table.Range(#"Expanded WorkBook",SheetorTableName,1)
			else if Value.Is(SheetorTableName, type list) and List.Count(SheetorTableName) = 2 then
				Table.Range(#"Expanded WorkBook",SheetorTableName{0},SheetorTableName{1})
			else if Value.Is(SheetorTableName, type list) and List.Count(SheetorTableName) > 2 then
					F[SelectListOfRows](#"Expanded WorkBook")
			else if Value.Is(SheetorTableName, type list) and List.Count(SheetorTableName) = 1 then
						Table.Range(#"Expanded WorkBook",SheetorTableName{0},1)
			else #"Expanded WorkBook"
		otherwise
			#"Expanded WorkBook",

	#"Filtered Sheet or Table" =
		try
			if Value.Is(SheetorTableName, type text) then
				Table.SelectRows(#"Kept Range of Rows", each Text.Contains([Sheet Name],SheetorTableName, Comparer.OrdinalIgnoreCase))
			else if Value.Is(SheetorTableName, type list) then
				Table.SelectRows(#"Kept Range of Rows", each List.Contains(SheetorTableName,[Sheet Name]))
			else
				#"Kept Range of Rows"
		otherwise
			#"Kept Range of Rows",

	SortedFiles = Table.Sort(#"Filtered Sheet or Table",{
		{"Import Date", Order.Descending},
		{"File Name", Order.Ascending},
		{"Kind", Order.Ascending},
		{"Sheet Name", Order.Ascending}}),

	#"Added WorkSheet" = Table.AddColumn(SortedFiles, "WorkSheet", each
		if [Data]=null then
			[CSV]
		else
			[Data],
		type table),

	#"Removed Other Columns" = Table.SelectColumns(#"Added WorkSheet",{"Import Date", "File Name", "Attributes", "Sheet Name", "Kind", "WorkSheet"}),

	PostFiltered =
		if PostFilter <> null then
			PostFilter(#"Removed Other Columns")
		else
			#"Removed Other Columns",

	#"Expanded Attributes" = Table.ExpandRecordColumn(PostFiltered, "Attributes", {"Kind", "Size"}, {"File Type", "Size"}),

	#"Changed Type" = Table.TransformColumnTypes(#"Expanded Attributes",{
		{"Sheet Name", type text},
		{"Kind", type text},
		{"Size", Int64.Type},
		{"File Type", type text}}),

	#"Grouped Rows" =
		if Fold <> null and Fold then
			Table.Group(#"Changed Type", {"WorkSheet"}, {{"File Attributes", each _, type table}})
		else
			#"Changed Type"
in
	#"Grouped Rows"