Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

File Transformation of csv files in Folder

I have read multiple articles that say they provided a solution to the issue I am having but they are not working.  I have a Folder that has mulitple CSV Files that are all Formatted the same.  I direct Power BI to get these files using the "From Folder" Option.  After I choose "Combine & Edit", I then set up all the steps I would like Power BI to perform to each file under the "Transform Sample File from "Folder Name"".  The most important and first step which is "Remove Top Row" is only being applied to the Sample File and not every other CSV File in the Folder.  It still keeps the top row from every other CSV File in the Folder causing my report to error out when I refresh it.  Am I missing a step somewhere or is this not possible with CSV Files?  I am downloading these files directly from a FTP so I have no control over the File extension.

Need Help

4 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    You can filter the extension. If you look to the tops applied steps, you can see the metadata about your folder. Just filter the extension there.

    The transformations applied to the Sample File are applied to the other tables.  Maybe the layout is different for those csv files ?

    Can you check it ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      All of the CSV Files are formatted the same because they are Generated automatically from a program.  Here is an example of how every file will look.  There are many more columns but the first line only has data to Column O

       

      HDR20200517INVOICE NUMBERACCOUNT NUMBER COMPANY ADDRESSPORTLANDOR97201USINVOICE AMOUNT9658
      DTL5118831105.34178E+20 HANDFULUSSEA153417822020051204.12121E+15119585339.37487E+21
      DTL5118831205.34178E+20 HANDFULUSSEA153417822020051204.12121E+15119541449.37487E+21
      DTL5118831305.34178E+20 HANDFULUSSEA153417822020051204.12121E+15119584969.37487E+21

       

      I always want to remove this first line off every CSV File because it is not needed.  This line is just a summary of the Invoice and does not relate to the rest of the file.

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        Anonymous ,

         

        Check this file: Download PBIX 

         

        I just created 3 csv, and excluded the 1 row for each file.

         

        Here the files: Download CSV Files 

         

        Just change the Source step on the Example CSV:

         

        = Folder.Files("D:\Google Drive\Exemplo PBI\Example CSV") to:

        = Folder.Files(YOUR_PATH)

         

        If you are doing the same, please, the m-code applied to both tables (sample and result).