Can I combine the data in multiple sheets(different tabs) of an excel workbook.
Suppose if I have sheets like these, is it possible to combine the data in these sheets.
I understand if the data is in different excel workbook, with same sheet name, it can be combined, using the “Folder/Sharepoint Folder”, provided the structure is same.
But from the same excel workbook, can it be combined
Hi @Anu
Yes it can be combined. The best way is that you open a blank query and write the below code to all the data from the workbook and filter those required.
Source = Excel.CurrentWorkbook()
You will get list of tables and extract the content first and then combine it.
Hi @Anu,
@MK3010 is 100% correct if you want that done in Excel.
Note that if you want to pick up new sheets but not the consolidated one you’ll have to exclude that by adding a filter - so to exclude that specific sheet name.
Here’s a video on how to achieve this in Power BI.
I hope this is helpful
Could you please attach a sample pbix. Along with the source file.
No, you didn’t provide any data…
And for your learning I would strongly suggest you try it yourself as demonstrated in the video.
My file is in sharepoint and this is how the advanced editor looks like now.
Where to make this change
Source = Excel.CurrentWorkbook()
Hi @Anu
As you are using sharepoint to connect to file then once you filter the required workbook then you can use below M code to extract the workbook content and then you can expand it. From there on you can follow the steps shown in the video to combine the files.
GetWbContent = Table.AddColumn(PreviousStep, "Custom", each Excel.Workbook([Content],false))
RemoveOtherColumn = Table.SelctColumns( GetWbContent, "Custom")
After this you expand the custom column. Below is the screen shot which I built with one of the sharepoint file.
Hi @Anu,
You can refer the attached pbix file. You can do rest of the transformation as per your need.
eDNA -Combine data Sharepoint( wb multi sheet).pbix (101.0 KB)
Thanks
Mukesh
Dear Mukesh,
Thanks for your time!
Also , if I am following your pbix attached, should these steps be added?
GetWbContent = Table.AddColumn(PreviousStep, “Custom”, each Excel.Workbook([Content],false))
RemoveOtherColumn = Table.SelctColumns( GetWbContent, “Custom”)
Also, since the dates are getting added as columns, will there be any limit on no.of columns in the power query.
Can unpivoting(the dates) be performed before merging the files?
This video makes it much more easier to understand 
Hi @Anu,
Added Custom— this step promote headers for each table.
Added Custom 1 — this step removes all the columns which is starting with “Column” i.e. Column34, Column35…
Yes because you are trying to pull data from Sharepoint so you combine it.
Hope the above solution helps you to understand the video and logic to implement.
Thanks
Mukesh
And this?
No there will be no limit. It’s a dynamic you can look at the step “ColumnName”
Yes you can do that. You can try to follow the pattern and transform as per your needs.
Thanks
Mukesh
One last question,
If there are multiple such files (with multiple tabs, and different tab names) in a folder? How to make query change?
At the top make sure to filter for the required files and then again get the content of the workbook and then filter for the required tabs. But the above logic will work.
I did the above mentioned steps, but this step is extremely slow. Anyways to improve performance?
let
Source = SharePoint.Files(“https://properties.sharepoint.com/”, [ApiVersion = 15]),
#“Filtered Rows” = Table.SelectRows(Source, each [Folder Path] = “https://properties.sharepoint.com/Shared Documents/Finance/New Kariba Bank Balances/”),
#“Removed Other Columns” = Table.SelectColumns(#“Filtered Rows”,{“Content”}),
#“GetWbContent” = Table.AddColumn(#“Removed Other Columns”, “Custom”, each Excel.Workbook([Content],false)),
#“Removed Other Columns1” = Table.SelectColumns(#“GetWbContent”,{“Custom”}),
#“Expanded Custom” = Table.ExpandTableColumn(#“Removed Other Columns1”, “Custom”, {“Name”, “Data”, “Item”, “Kind”, “Hidden”}, {“Custom.Name”, “Custom.Data”, “Custom.Item”, “Custom.Kind”, “Custom.Hidden”}),
#“Filtered Rows1” = Table.SelectRows(#“Expanded Custom”, each ([Custom.Kind] = “Sheet”) and ([Custom.Item] <> “Template”)),
#“Removed Other Columns2” = Table.SelectColumns(#“Filtered Rows1”,{“Custom.Name”, “Custom.Data”}),
#“Renamed Columns” = Table.RenameColumns(#“Removed Other Columns2”,{{“Custom.Name”, “Name”}, {“Custom.Data”, “Data”}}),
#“Added Custom” = Table.AddColumn(#“Renamed Columns”, “PromoteHeaders”, each Table.PromoteHeaders([Data], [PromoteAllScalars=true])),
#“Added Custom1” = Table.AddColumn(#“Added Custom”, “Custom”, each Table.RemoveColumns([PromoteHeaders], List.Select(Table.ColumnNames([PromoteHeaders]), each Text.StartsWith(_,“Column”)))),
#“Removed Other Columns3” = Table.SelectColumns(#“Added Custom1”,{“Name”, “Custom”}),
ColumnName = FxDistinctColumnNames(#“Removed Other Columns3”,1),
Custom1 = #“Removed Other Columns3”,
#“Expanded Custom1” = Table.ExpandTableColumn(Custom1, “Custom”, ColumnName, ColumnName),
#“Filtered Rows2” = Table.SelectRows(#“Expanded Custom1”, each ([Currency] <> “Currency”) and ([Account Name] <> null)),
#“Reordered Columns” = Table.ReorderColumns(#“Filtered Rows2”,{“Name”, “Bank”, “Account Name”, “Currency”, “Type”, “Account Type 2”, “Account No. (Kyriba)”, “Account Number”, “Bot Remarks”, “Comments”, “10/11/2020”, “10/12/2020”, “10/13/2020”, “10/14/2020”, “10/18/2020”, “10/21/2020”, “10/22/2020”, “10/23/2020”, “10/24/2020”, “10/25/2020”, “10/26/2020”, “10/27/2020”, “10/28/2020”, “10/29/2020”, “10/30/2020”, “10/31/2020”, “11/1/2020”, “11/2/2020”, “11/3/2020”, “11/4/2020”, “11/5/2020”, “11/6/2020”, “11/7/2020”, “11/8/2020”, “11/9/2020”, “11/10/2020”, “11/11/2020”, “11/12/2020”, “11/13/2020”, “11/14/2020”, “11/15/2020”}),
#“Unpivoted Other Columns” = Table.UnpivotOtherColumns(#“Reordered Columns”, {“Bank”, “Name”, “Account Name”, “Currency”, “Type”, “Account Type 2”, “Account No. (Kyriba)”, “Account Number”}, “Attribute”, “Value”)
in
#“Unpivoted Other Columns”
Hi @Anu,
See if this helps performance.
let
Source = SharePoint.Files("https://properties.sharepoint.com/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each [Folder Path] = "https://properties.sharepoint.com/Shared Documents/Finance/New Kariba Bank Balances/"),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
#"GetWbContent" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content],false)),
#"Removed Other Columns1" = Table.SelectColumns(#"GetWbContent",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom.Kind] = "Sheet") and ([Custom.Item] <> "Template")),
#"Removed Other Columns2" = Table.SelectColumns(#"Filtered Rows1",{"Custom.Name", "Custom.Data"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns2",{{"Custom.Name", "Name"}, {"Custom.Data", "Data"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "PromoteHeaders", each Table.PromoteHeaders([Data], [PromoteAllScalars=true])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Table.Buffer( Table.RemoveColumns([PromoteHeaders], List.Select(Table.ColumnNames([PromoteHeaders]), each Text.StartsWith(_,"Column"))))),
#"Removed Other Columns3" = Table.SelectColumns(#"Added Custom1",{"Name", "Custom"}),
ColumnName = FxDistinctColumnNames(#"Removed Other Columns3",1),
Custom1 = #"Removed Other Columns3",
#"Expanded Custom1" = Table.ExpandTableColumn(Custom1, "Custom", ColumnName, ColumnName),
#"Filtered Rows2" = Table.SelectRows(#"Expanded Custom1", each ([Currency] <> "Currency") and ([Account Name] <> null)),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows2",{"Name", "Bank", "Account Name", "Currency", "Type", "Account Type 2", "Account No. (Kyriba)", "Account Number", "Bot Remarks", "Comments", "10/11/2020", "10/12/2020", "10/13/2020", "10/14/2020", "10/18/2020", "10/21/2020", "10/22/2020", "10/23/2020", "10/24/2020", "10/25/2020", "10/26/2020", "10/27/2020", "10/28/2020", "10/29/2020", "10/30/2020", "10/31/2020", "11/1/2020", "11/2/2020", "11/3/2020", "11/4/2020", "11/5/2020", "11/6/2020", "11/7/2020", "11/8/2020", "11/9/2020", "11/10/2020", "11/11/2020", "11/12/2020", "11/13/2020", "11/14/2020", "11/15/2020"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Bank", "Name", "Account Name", "Currency", "Type", "Account Type 2", "Account No. (Kyriba)", "Account Number"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"still very slow:(
And you are sure it’s this #“Expanded Custom1” step not #“Filtered Rows2” ??
Are your files in the main folder of your document library in SharePoint?
Some food for thought…