Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
maasool
Helper I
Helper I

Filter csv file before importing data

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?

4 REPLIES 4
AlexisOlson
Super User
Super User

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.

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...

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:

AlexisOlson_0-1666795873774.png

Waldo35
Helper I
Helper I

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors