Filter csv file before importing data

[maasool]

Hello

I have a very large csv file, which I like to import with Power Query.

However, I'd like to import only selected data from the file (not import everything and filter it afterward).

For example, I'd like to import only data, where column YEAR in the csv file has a value of 2020. 

Is it possible?

[AlexisOlson]

A CSV is a flat file format where there isn't a good way to filter it without scanning it, so you have to at least scan the whole file even if you don't actually load the whole file. If the file is too big for Power Query to scan in a reasonable amount of time, you'll need to pre-process it or filter it with another tool (like Python as @Waldo35 mentioned) that can scan more efficiently.

[maasool]

Is it still a csv file, when it is accessed through a https://... link? Or is it possible to pre-filter such connections?

https://data.gov.lv/dati/dataset/8d31b878-536a-44aa-a013-8bc6b669d477/resource/50ef4f26-f410-4007-b2...

[AlexisOlson]

It depends on the link. If it's just pointing to a file, then you've got to download it before filtering. You can only filter at the source if the source has some sort of data query engine that can do filtering.

Often data sources will provide more granular access via an API so that you can pass filters via URL. I think this might work for the resource you linked to. Clicking on the green Data API button on this page gives these suggestions:

[Waldo35]

Power Query usually is that prestep where u filter and transform data beforehand so that the dashboard itself is quick even tho it takes a while to load the data.

You could try loading it with Python code and using pandas to filter out the csv.

Would look something like this:

import pandas as pd
df = pd.read_csv ('file_name.csv')

df= df[df['YEAR'] == 2020]

For more info

pandas df docs