SOLVED

Power Query Filtering

Copper Contributor

Hi,

I have 16 workbooks, each with 6 worksheets. The size of each workbook is approximately 30Mb. I update the workbooks weekly right now because it is rather tedious to collect the data to generate the results.

 

I use power query to filter for observations obtain over the past 13 days from each workbook. Each time I have to select the dates for each workbook for the observations I require, which means I have to do it 16 times, selecting the dates I require one by one for each workbook, even though they are consecutive observations (16 workbooks * 13 observations = 208 times).

 

I'd like to expand my database but only if there was a simpler process. Can anybody help me with a solution?

 

Regards

kaywai

 

4 Replies
best response confirmed by kaywai (Copper Contributor)
Solution

@kaywai 

If your workbooks have the same structure you may use From Folder connector to do all transformation on the sample file and apply to all workbooks combining the result after that. 

@Sergei Baklan 

Hi Sergei,

I did as you suggested which was an excellent suggestion.

 

All the 16 files I wanted, I had selected. Then I got to Filtered Rows and selected the dates I required. I got to the "limit of 1000 values reached". I proceeded nonetheless. The preview only loaded 5 of 16 files arbitrarily. And when it came to loading time, it also only loaded 5 of the 16 files. 

 

Any ideas how to fix this?

 

Regards

Kay Wai

@Sergei Baklan
Sorted the issue Sergei. It had to do with calendar formatting between "General" and "Text" that was causing the issue.

Thx for your help and idea! Appreciate it!

Regards
Kay Wai

@kaywai , glad it helped

1 best response

Accepted Solutions
best response confirmed by kaywai (Copper Contributor)
Solution

@kaywai 

If your workbooks have the same structure you may use From Folder connector to do all transformation on the sample file and apply to all workbooks combining the result after that. 

View solution in original post