Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Holly Kramer]

Data Model best practice help please!

I have an Excel source file that currently contains only 2 tabs that are formatted to work in our Power BI dashboard.  As time goes on, more tabs will be added (20+) that match this same format.  My plan is to append the current 2 tabs into one query – column wise they are identical.

My question – how do I best bring in the future NEW Excel tabs into this SAME query?  I presume we’d want to use the same query since all data needs to be viewed as a whole and would have the same transformations done. 

These tabs won’t all be done at one time.  They each represent one of our offices, and over time, we’ll add more as our firm grows.

Tips and tricks are greatly appreciated.  Thank you in advance!

——————————
Holly Kramer
Digital Design Application Specialist
Interior Architects
Houston TX
713-481-3161
——————————

[Riccardo Perico]

Good Morning Holly,

When you select the xlsx as source “Get Data > Excel > Select File” you’ll be presented with the list of the tabs inside the file. 
Instead of choosing tabs and importing, right-click on the folder with the name of the Excel Workbook and in the context-menu push “Edit”.
This opens power query with the only Excel.Workbook M command that indicates the connection to the file and exposes to you the workbook content.
Remove all the columns but “Data” and then use “Expand”, this will create a table with rows of all sheets. 
Promote the first row as header and remove rows that contains headers coming from other sheets.

This is dynamic because it picks up the Excel.Workbook results during refresh and then applies the other transformation.

below an M snippet example:

let
    Source = Excel.Workbook(File.Contents("C:tempzzz_Footballdatasetsall-euro-data-2017-2018.xlsx"), null, true),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", 
 ... "Column65"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", ... "Data.Column65"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Div", type text}, {"Date", type date}, {"HomeTeam", type text}, {"AwayTeam", type text}, {"FTHG", Int64.Type}, {"FTAG", Int64.Type}, {"FTR", type text}, ... {"PSCA", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Div] <> "Div"))
in
    #"Filtered Rows"?

Hope this helps.

regards,
Rik

——————————
Riccardo Perico
Data Platform & BI Specialist @ SolidQ
——————————
——————————————-

[Mike Hammons]

Hi Holly,

Another option, and in my opinion easier, is to separate the worksheets into different Excel files. When a new office comes online the Excel file for that office is simply dropped into a shared folder. Then, it’s pretty easy to use Power BI Desktop to Get Files, More, Folder, and then choose that folder. 

I did a quick video to show you what I am talking about. 

https://vimeo.com/351175109

Mike

——————————
Mike Hammons
Director, Business Intelligence
AKA Enterprise Solutions
Woodstock GA
770-926-5020
——————————
——————————————-

[Holly Kramer]

Thank you both for your responses.  One clarification, our Excel file lives on SharePoint so that reps from each office can easily collaborate.  Rik’s suggestion may work out best as we always worry about people creating brand new files and stashing somewhere.

I’m trying to work through our SharePoint connection now to see if the same option to “Edit” the source exists for a SharePoint hosted file.  I would hope so since it is Excel.

In the past I’ve successfully used Web data source with the URL path to the file, but now I’m getting an “Access to the resource is forbidden” error.  Even when connecting to a Web-Excel data source that is currently working.

Do either of you know if Power BI recently changed the way we connect to a SharePoint hosted Excel file?

——————————
Holly Kramer
Digital Design Application Specialist
Interior Architects
Houston TX
713-481-3161
——————————
——————————————-

[Mike Hammons]

Hi Holly,

You can use the folder method I suggested with SharePoint as well….instead of choosing Folder, use SharePoint Folder and follow the same instructions after that. Note the URL needs to be the root of the site. What we like about the separate files is it reduces the accidental updating by end users of the wrong tab. Either approach is fine and accomplishes what you need.

I am not aware of any changes. I just used Get File>Web and then entered the URL for my Excel spreadsheet. Did you remove the last part of the URL? https://tenant.sharepoint.com/sites/Team/Documents/Territory/Territory1.xlsx?web=1

There might be cached credentials. Try and clear them and reconnect to your source. 
Open Power BI, click Edit Queries, click Data Source Settings, this will show you a listing of all of your cached settings. Click the Global Permissions radio button, then scroll to your SharePoint/Web connection and click the Clear Permissions button, then click OK.

Mike

——————————
Mike Hammons
Director, Business Intelligence
AKA Enterprise Solutions

——————————
——————————————-

[Holly Kramer]

Hi Mike – yes I figured out it was the cached credentials, but it took me a bit to find how to clear them out.  Thank you for the feedback.  If I ever forget again, this will be easy to refer to again!

——————————
Holly Kramer
Digital Design Application Specialist
Interior Architects
Houston TX
713-481-3161
——————————
——————————————-

[Hasham Niaz]

HI !

Although your question has been answered above, try creating a separate .xlsx file for each office.

Then use the Share Point Folder connection from PBI Desktop to get the .xlsx files.

It would be easy to debug in future if some of the offices use different naming which causes error in your data load.

Regards,

——————————
Hasham Bin Niaz
Director Data & Analytics
Karachi, Pakistan
——————————
——————————————-

[Riccardo Perico]

Holly, 

in the end, please share the solution you decided to use and if it works as expected.

——————————
Riccardo Perico
Data Platform & BI Specialist @ SolidQ
——————————
——————————————-

[Holly Kramer]

Hello All – thank you for the helpful feedback!  

We decided to use the Edit Workbook option Rik suggested to bring in future tabs.  The rest was figuring out the best sequence to remove rows so that we could ‘exclude’ certain Excel tabs needed in the workbook but not in Power BI (like Drop Down menus).

——————————
Holly Kramer
Digital Design Application Specialist
Interior Architects
Houston TX
713-481-3161
——————————
——————————————-

[Riccardo Perico]

Thanks for sharing the complete solution.

regards!

——————————
Riccardo Perico
Data Platform & BI Specialist @ SolidQ
——————————
——————————————-