Forum Discussion

admin11's avatar
admin11
Memorable Member
4 years ago
Solved

How to load only 1 CSV file By modify the script ?

Hi All

 

I have load 4 CSV file to sharepoint folder. The script as below :-

 

let
Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/FTGL/", [ApiVersion = 15]),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"GL Code", Int64.Type}, {"GL Description", type text}, {"Type", type text}, {"Month", Int64.Type}, {"Year", Int64.Type}, {"Posting Date", type date}, {"AccCur", type any}, {"AccConvRate", Int64.Type}, {"C/D", type number}, {"CurrencyValue", Int64.Type}, {"Reporting Code", Int64.Type}, {"Date", type date}})
in
#"Changed Type"

 

My question is , how to modify the above script , so that it will only load 1 CSV file instead now load 4 CSV file.

 

 

The reasons is , i try to create sample PBI file , so that it can load faster , until i tested okay , then i load all raw data file. My actual raw data file have more then 100.

 

Paul Yeo

  • admin11 

    You have 2 ways to do this

    1st Way- Re import your Folder from Sharepoint and do not click on "Combine" data since you only need 1 table ,

     you will get this in Power Query

    Click on Binary of the File you want to keep and then Table

    you will be set

     

    2nd way is: since you already combined the 4 files then go to this step

    Click on Binary and then Table (Like i explained on the 1st way) , then click on "Continue"and you will be all set.

     

     

     

6 Replies

  • You can insert a step after #"Filter Hidden Files1" to select just the top row or modify the code to add.

     

    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true){0},

     

    This returns the index 0 (first) row of the table.

    • admin11's avatar
      admin11
      Memorable Member

      AlexisOlson 

      Thank you and happy new year to you. 

      After i insert {0} 

      I get error msg :-

      I unable to clear the error.

       

      Paul

      • aj1973's avatar
        aj1973
        Community Champion

        Hi admin11 

        Delete these steps and re do them again if you need them.