cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonygd Frequent Visitor
Frequent Visitor

Skip rows in CSV before it is parsed by Power BI

Hi, I have a number of reasonably large .csv files. Power BI is great at analysing them but only if I remove a number of rows of data (using Excel) from the top of each file before loading it in to Power BI. The reason I have to remove them is that these rows are effectively header info but they are in a number of different formats along each row and when Power BI attempts to parse the data it uses the first row to determine what it expects the rest of the data to be. This results in the actual data being parsed at the "wrong" comma and ending up useless, unless as I say I "pre-process" the data in Excel.

 

Is there any way (in Power BI) to strip out these header rows and only then parse the rows which compromise the rest of the data?

 

Thanks,

Tony

1 ACCEPTED SOLUTION

Accepted Solutions
tonygd Frequent Visitor
Frequent Visitor

Re: Skip rows in CSV before it is parsed by Power BI

Hi Tom, Lydia,

 

Unfortunately neither of your suggestions fixed the problem I was experiencing but I have found a solution.

 

The .csv files I had been working with are generated as output from a portal and I had been trying to load them directly into PBI.

 

It seems that if I open the .csv in Excel and save it (without making any changes) I can then load it into PBI with no problem...

 

Not sure why this works, but it does...

 

Thanks for your suggestions,

Regards,

Tony

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Skip rows in CSV before it is parsed by Power BI

Hey Tony,

 

there is a way in Power Query

  1. Import the file
  2. Mark the complete table and choose "Remove Top Rows" from the context menu

How to skip Top Rows.png

 

I guess this is what your are looking for

 

Regards

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
tonygd Frequent Visitor
Frequent Visitor

Re: Skip rows in CSV before it is parsed by Power BI

Hi Tom,

 

Thanks for the suggestion. Unfortunately that doesn't do the trick because the rest of the rows have already been parsed by Power BI based on what it detected in the first row..  Smiley Frustrated .

 

The challenge is that the "real data" is about 40 columns whereas the "header" is only about 6 columns so Power BI parses based on 6 columns meaning the the real data is wrapped and displaced...

 

I really need some way of jumping over the 1st "n" rows before the data is parsed.

 

Cheers,

Tony                                                    

Super User
Super User

Re: Skip rows in CSV before it is parsed by Power BI

Hey,

 

not sure what you mean by displaced. I created a sample file with

20 rows of garbage at beginning (3 columns)

10000 empty rows

10 valuable rows with 40 columns

 

The table contains 40 columns, of course the 10021st row has to be promoted as header and you also have to manually change the datatype of the column, but this has to be done just once and it's always a good idea to check the datatype.

 

Maybe you have to check the option "Data Type Detection" (this was not necessary during my testing)

Skip Rows - Configure Import - 02.png

 

Cheers

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Moderator v-yuezhe-msft
Moderator

Re: Skip rows in CSV before it is parsed by Power BI

@tonygd,

After you import data from CSV to Power BI Desktop, in Query Editor, remove all steps except “Source” step in “APPLIED STEPS”, then use “Remove Top Rows” feature to remove rows.
1.JPG

If the above step doesn’t help, please share us sample CSV file and post expected result.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tonygd Frequent Visitor
Frequent Visitor

Re: Skip rows in CSV before it is parsed by Power BI

Hi Tom, Lydia,

 

Unfortunately neither of your suggestions fixed the problem I was experiencing but I have found a solution.

 

The .csv files I had been working with are generated as output from a portal and I had been trying to load them directly into PBI.

 

It seems that if I open the .csv in Excel and save it (without making any changes) I can then load it into PBI with no problem...

 

Not sure why this works, but it does...

 

Thanks for your suggestions,

Regards,

Tony

View solution in original post

Moderator v-yuezhe-msft
Moderator

Re: Skip rows in CSV before it is parsed by Power BI

@tonygd,

Glad to  hear the issue is solved,  you can mark your reply as answer. That way, other community members would easily find the answer when they get same issues.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jimmy801 Member
Member

Re: Skip rows in CSV before it is parsed by Power BI

Hello

Still have the issue?
Would like to help you.

Br

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)