Combine Multiple Tabs & Multiple WorkBooks Through Power Query

[snaplink22]

Hello,

I've been struggling with getting Power Query to combine 2 workbooks, with 2 tabs each. Here is my below setup:

1. Both workbooks are located in the same file folder on my desktop and are both .xlsx files.
2. Each tab's data has been converted to a table, with each table being named with the same prefix (WASS_) and the tabs titles follow the same naming convention. Each table and each tab has a different ending to their respective names however (e.g. WASS_Lindrick or WASS_Pasco)

3. On a new workbook, I select Data, Get Data, From File, From Folder. I select browse and then select the folder where these workbooks are stored.

4. Here's where things get a bit murky. I've seen a variety of videos and articles that reference what I'm supposed to do next, with each one being different. The options I have available are:
Combine drop down to select: "Combine & Transform, Combine & Load, and Combine and Load To"
Load drop down to select: "Load and Load To"
"Transform Data"

5. I've experimented with all of them, but the consequences seems to be "Transform Data". Which in turns give me this menu:

6. I removed all columns except the first two and then click on the double arrows under the Content header and select the table WASS_Lindrick1:

This, however, is where it deviates from all the videos as I only see 1 tabs worth of data displayed after clicking OK. Every thing that I've researched indicates I should be seeing all the data from all 4 of my tabs now, but I'm only seeing the first tabs data.

Any assistance would be greatly appreciated!

 

[sandy666]

or analyse this code and adapt to your needs

let
    Source = Folder.Files("path_to_folder"),
    ROC = Table.SelectColumns(Source,{"Content", "Name"}),
    Content0 = ROC{0}[Content],
    Import0 = Excel.Workbook(Content0),
    ROC0 = Table.SelectColumns(Import0,{"Name", "Data"}),
    Expand0 = Table.ExpandTableColumn(ROC0, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6"}),
    Content1 = ROC{1}[Content],
    Import1 = Excel.Workbook(Content1),
    ROC1 = Table.SelectColumns(Import1,{"Name", "Data"}),
    Expand1 = Table.ExpandTableColumn(ROC1, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6"}),
    Append = Table.Combine({Expand0, Expand1}),
    Filter = Table.SelectRows(Append, each ([Data.Column1] <> null)),
    Sort = Table.Sort(Filter,{{"Data.Column1", Order.Descending}}),
    RTR = Table.Skip(Sort,4),
    Promote = Table.PromoteHeaders(RTR, [PromoteAllScalars=true]),
    Type = Table.TransformColumnTypes(Promote,{{"Sheet1", type text}, {"Week 1", Int64.Type}, {"Week 2", Int64.Type}, {"Week 3", Int64.Type}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}}),
    Rename = Table.RenameColumns(Type,{{"Sheet1", "Sheet"}}),
    Result = Table.Sort(Rename,{{"Sheet", Order.Ascending}})
in
    Result

 

[GraH]

Hi sandy666,
Out of thread context. Your avatar logo is the Brussels Iris flower, hence the "From highway to hell"?

 

[sandy666]

Hi sandy666,
Out of thread context. Your avatar logo is the Brussels Iris flower, hence the "From highway to hell"?

why not?
my location is changing often

 

[snaplink22]

That screen offers to select a "sample" from which it creates a function. That function is then applied on the other workbooks. It can be handy but generally speaking I don't like that.
[/QUOTE]

MarH, is there another way to create that function instead of using the Combine option? I created a sample set of documents to work with as the other files were large and contained company data. After I added the Custom Column I then expanded it (Table1 corresponds with WA_ONE and so on). My guess is the next step would to be to right-click the the (table) Test icon on the far left and select Create Function, then select Advanced Editor once it has been created to pull up the (M?) code.