Forum Discussion

Frixel's avatar
Frixel
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Problem to combine multiple files with different sheet names.

Hi All,   I have a problem with combine multiple files from Sharepoint folder.   Al the files are in one folder and have all the same name and colomn name/format. But each file, the tab also has...
Need Help
  • AilleryO's avatar
    AilleryO
    5 years ago

    Hi,

     

    Did you load your file, using connection Folder or Excel ?

    If you create a connection to a folder, you should have a function (fx), parameters, and a query in the left panel.

     

    The solution you pointed out is in M code (Power Query) and need to be copy and paste on the formula bar (new steps) or in the advanced editor.

     

    But I can offer you a more simple solution, if...

    you always need the first sheet  (1st sheet = 0, 2nd=1...) of your Excel files, you can also use :

    #"Sales_Sheet" = Source{0}[Data]

    instead of

    #"Sales_Sheet" = Source{[Item="Sales Week 32",Kind="Sheet"]}[Data]

     

    Hope this helps you, let us know

AilleryO's avatar
AilleryO
Icon for Memorable Member rankMemorable Member
5 years ago

First open Power Query Editor

You need to find the step where your Excel sheet is called by its name, the step with the Source function.

It can be in your query or maybe in a function depends on your connection.

Or look directly in advanced editor and find a line with Source{[Item="nameofyourfile",Kind="Sheet"]}[Data]

and replace it with :

Source{0}[Data]

Without your query i cannot tell you where it is, but you'll find it 🙂