Forum Discussion

tonygd's avatar
tonygd
Regular Visitor
9 years ago
Solved

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 P...
Need Help
  • tonygd's avatar
    tonygd
    9 years ago

    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

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
9 years ago

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

 

I guess this is what your are looking for

 

Regards

  • tonygd's avatar
    tonygd
    Regular Visitor
    9 years ago

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

     

    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                                                    

    • TomMartens's avatar
      TomMartens
      Icon for Super User rankSuper User
      9 years ago

      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)

       

      Cheers

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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.


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

      Regards,
      Lydia

      • tonygd's avatar
        tonygd
        Regular Visitor
        9 years ago

        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