Forum Discussion

MichaelF1's avatar
MichaelF1
Helper III
4 years ago
Solved

Changing Excel sheet name in Power Query

Hi Everyone   I have a Power Query data source that is drawing multiple excel files from the same folder. I need to add to the files in the folder to capture the latest monthly sales data for my or...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    MichaelF1 The code change needed is to the Transform File function.

     

    It should look similar to this:

    This is where you can modify it as Vijay_A_Verma helpfully suggested.

ronrsnfld's avatar
ronrsnfld
Super User
4 years ago

It would be helpful if you could post the code from the Advanced Editor (as text, not as a screenshot).

MichaelF1's avatar
MichaelF1
Helper III
4 years ago

Hi ronrsnfld ,

Here you go...

let
Source = Folder.Files("my_file_path\sales_orderdate"),
#"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}, {"SalesOrders.CompanyCode", Int64.Type}, {"Company Name", type text}, {"SalesOrders.CreatedDate", type date}, {"SalesOrders.CustOrdRef", type text}, {"SalesOrders.OrderNumber", Int64.Type}, {"SalesOrders.OrderType", type text}, {"SalesOrders.AccountCode", type text}, {"vSalesOrders.vOrderClass", type text}, {"Vcreatedtime", type datetime}, {"SalesOrders.OrderValue", type number}})
in
#"Changed Type"

 

Thank you

 

Michael

 

 

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    4 years ago

    Hi MichaelF1 ,

     

    You are looking at the query that applied invoke function. when you use the "Import from folder" function, PQ will create sample file, transform file, and the invoked query (i.e. Test3 in your case).

     

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),

     

    You will need to look at the "Transform Sample file" query as per Vijay_A_Verma note.

     

    if you post the code for the Transform Sample file, and we will be able to guide you from there.

     

    Regards

    KT