Forum Discussion

MichaelF1's avatar
MichaelF1
Icon for Helper III rankHelper III
4 years ago
Solved

How to edit sheet name in Excel doc when source is a folder

Hi everyone,   I have a PBI report that uses a folder of excel docs as the data source.   I want to change the excel docs and I think I need to change the name of the actual tab in the excel file...
Need Help
MichaelF1's avatar
MichaelF1
Icon for Helper III rankHelper III
4 years ago

amitchandak My datasource is a folder, not a single file:

 

let
    Source = Folder.Files("C:\Users\.....\PowerBIData\data sources\moves_adjusts"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File (2)"([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 (2)"(#"Sample File (2)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"TransactionDate", type date}, {"TransactionTime", type time}, {"TransactionType", type text}, {"Tra Text 1", type any}, {"ItemCode", type text}, {"ProductCode", type text}, {"NumberOf", Int64.Type}, {"LocaCodeFrom", type text}, {"LocGroupCodeFrom", type text}, {"LocaCodeTo", type text}, {"LocGroupCodeTo", type text}, {"Operatorcode", type text}}),

 

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, MichaelF1 

 

 

What do you mean by name of the actual tab? 

 

Not sure what your needs are, Please add more details so we can help you soon.

 

Best Regards,
Community Support Team _ Janey

 

  • MichaelF1's avatar
    MichaelF1
    Icon for Helper III rankHelper III
    4 years ago

    Hi Janey, thanks for getting back to me. 

    I mean the name of the actual sheet in excel. (see picture)

    Thanks again

    Michael

     

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi, MichaelF1 

       

      When you click on the content of one specific xlsx, you will see the corresponding sheet name or table name. And you can change them. Please note that what you change is only the display in powerbi, powerbi won't change the content of the source data.

      Did I answer your question? Please mark my reply as solution. Thank you very much.
      If not, please feel free to ask me.

      Best Regards,
      Community Support Team _ Janey

       

      • MichaelF1's avatar
        MichaelF1
        Icon for Helper III rankHelper III
        4 years ago

        Hi v-janeyg-msft , thanks for your suggestion but unfortuantely this does not give me the result I need. If I click the Binary hyperlink as in your suggestion, I get the dialogue box below:

        If I click continue, then all my subsequent steps in power query are erased - so I have to start my query almost from scratch gain. I definitely don't want this!

        What I want is just to be able to change the name of the sheet that power query looks for, but keeps everything else in the query as it is.

         

        Thanks again,

         

        Michael