Power Query Appending and Filtering Multiple Files with Inconsistent Lines

[hayesb26]

Hi All-

First time posting here, I was unable to find this answer in a quick search but my apologies if it's been posted. We've recently started getting into Power query to automate repetitive processes at work - incredible tool. I look forward to spending a lot of time learning here. I have run into an issue though -- we have a handful of files that contain the same data for different units. However, the data is not on the same row/column of the file in each case.

I have uploaded the source files and filtered to present only the rows I'd like to see, but now I cannot get the data to aggregate in the same rows/columns for each category. You can see in the screenshot below that the rows 1 & 3 and 2 & 4 are the same, but I cannot figure out an easy/effective way to get them into just two rows (or columns, I've tried transposing a bunch of ways). Any tips or ideas?

 

[alansidman]

Show us a mock up of what you want this data to look like. Suggest in each case (above and mock up) you use XL2BB so we don't have to recreate your data. See my signature

 

[hayesb26]

Thanks - I appreciate the help. Here is what I've currently got:

Import File Testing 2.xlsx
	B	C	D	E
2	PROFIT AND LOSS ACCOUNT	PBT impact	PROFIT AND LOSS ACCOUNT	PBT impact
3
4			YTD	-8642397.43
5	YTD	2770118.47
6			ADC Elim Reserves	-227973.18
7	2020 BALANCES 31/12/2020	-6885382.05	Holiday pay accural EUG rev	-49503.9
8	ZLGAP	-12593	IBNR Elim	-221968
9			DCC W/H Cost Capitalisation	-759348.47
10	Write off Local Intang	-23263.99	1Q tax	0
11	Writeoff Month Labcap	-53136.38	2Q tax	0
12	Employee Accrual	-713000	3Q tax	0
13	Depreciation write off	-58905.78	4Q tax	0
14	Elim Mgt Ch	0	ADC Elim	0
15	Writeoff Month Labcap 2021 ADJ	0
16	Retirement obligation	552000	Subtotal	-9901190.98
17	Amortisation	989208.81
18			Per EUG	-9901190.98
19	1Q tax	0
20	2Q tax	0	Unreconciled variance	-1.18484E-08
21	3Q tax	0
22	4Q tax	0
23
24	Subtotal	-3434953.92
25
26	Per FRG	-3434953.92
27
28	Unreconciled variance	1.30706E-08
29
30
31
32	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
GAAP-STAT Power Query

The 'Profit and Loss Account' columns 1 and 3 contain the same type data that I'd like to be in one column, but they have different names and types so I don't want them to combine/merge, I want them to basically list below each other. The corresponding $ amounts are in columns 1 and 4. Ideally I'd be able to get something like this:

Import File Testing 2.xlsx
	B	C	D
35	PROFIT AND LOSS ACCOUNT	PBT impact	Entity
36	YTD	2770118.47	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
37			FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
38	2020 BALANCES 31/12/2020	-6885382.05	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
39	ZLGAP	-12593	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
40			FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
41	Write off Local Intang	-23263.99	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
42	Writeoff Month Labcap	-53136.38	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
43	Employee Accrual	-713000	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
44	Depreciation write off	-58905.78	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
45	Elim Mgt Ch	0	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
46	Writeoff Month Labcap 2021 ADJ	0	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
47	Retirement obligation	552000	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
48	Amortisation	989208.81	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
49			FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
50	1Q tax	0	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
51	2Q tax	0	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
52	3Q tax	0	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
53	4Q tax	0	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
54			FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
55	Subtotal	-3434953.92	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
56			FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
57	Per FRG	-3434953.92	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
58			FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
59	Unreconciled variance	1.30706E-08	FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx
60	YTD	-8642397.43	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
61			LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
62	ADC Elim Reserves	-227973.18	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
63	Holiday pay accural EUG rev	-49503.9	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
64	IBNR Elim	-221968	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
65	DCC W/H Cost Capitalisation	-759348.47	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
66	1Q tax	0	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
67	2Q tax	0	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
68	3Q tax	0	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
69	4Q tax	0	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
70	ADC Elim	0	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
71			LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
72	Subtotal	-9901190.98	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
73			LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
74	Per EUG	-9901190.98	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
75			LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
76	Unreconciled variance	-1.18484E-08	LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx
GAAP-STAT Power Query

 

[alansidman]

let
    Source = Table.Combine({Table1, Table2}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.StartsWith([PROFIT AND LOSS ACCOUNT], "FR5") then [PROFIT AND LOSS ACCOUNT] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.EndsWith([PROFIT AND LOSS ACCOUNT], "xlsx") then [PROFIT AND LOSS ACCOUNT] else null),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom1", {{"Custom.1", null}}),
    #"Filled Up" = Table.FillUp(#"Replaced Errors",{"Custom.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([PBT impact] <> "FR5 Cross Check PL (FRG) P09.21 run 23.9.21.xlsx" and [PBT impact] <> "LG3 Cross Check P&L (MS5) P12 for cross check diffs.xlsx"))
in
    #"Filtered Rows"

Append each table to the other produces your expected results.

 

[hayesb26]

I do not think I have the two data sources currently set up as separate tables. I basically imported a folder (the number of 'entity' files will vary each time) and filtered by the sheet name down to get the specific sheets for each, I'd want this query to scale up/down depending on the files that are in the folder.

Is the above able to accommodate that? Sorry like I said, I'm still new and have just done an introductory training and a handful of queries thus far.

 

[alansidman]

This should help