Import excel data in hierarchical folder structure

[rhytha]

Hi,

I am new to PowerBI and been working on it for last couple of weeks.

I have excel sheets with folder hierachy like this

Manager A

    ----Project 1

             ---- Productivity Report --> Excel Files

             ---- Pending Tasks Report --> Excel Files

             ---- Error Queries

                        --- Customer Queries --> Excel Files

                        --- Sales Queries--> Excel Files

    ----Project 2

             ---- Productivity Report --> Excel Files

             ---- Pending Tasks Report --> Excel Files

             ---- Error Queries

                        --- Customer Queries --> Excel Files

                        --- Sales Queries --> Excel Files

Manager B

    ----Project 3

             ---- Productivity Report --> Excel Files

             ---- Pending Tasks Report --> Excel Files

             ---- Error Queries

                        --- Customer Queries --> Excel Files

                        --- Sales Queries--> Excel Files

    ----Project 4

             ---- Productivity Report --> Excel Files

             ---- Pending Tasks Report --> Excel Files

             ---- Error Queries

                        --- Customer Queries --> Excel Files

                        --- Sales Queries --> Excel Files

I need to import the excel files with the above structure. The dashboard will have the following drop down

Manager     Projects    Reports     Month

Manager A   Project 1 x

Manager B   Project 2 x

                  Project 3 x

                  Project 4 x

But the excel files themselves do not have any manager or project details, they only have direct data with the folder specifying which manager and client can access it.

In above sceanrio how to import with access control list? That is only manager A and Client A can access data in excel files inside Manager A. Also the excel data has large number of excel files, the queries usually run into 2-3 GB data. But those only need table view which is ok, but will PBI be able to refresh such large data  every day?

Can anyone help me on this?

[Anonymous]

There seem to be a number of threads here.  It's also not clear to me the difference between the Productivity Report, Pending Tasks Report, and the Error Queries, nor how you derive Client from the structure you've outlined.

.

You could get the Manager and Project data into Power BI from the Folder Path as part of your data import - e.g.

1. Click New Source -> More... -> Folder and import from the top level of your folder structure, then click OK - you should see a table with Binary Content on the left and details of each files down through your folder structure
2. Filter files as appropriate to get all the ones of the same structure (e.g. do you ony need the Productivity Reports?) 
3. Then click the "double down" arrow on Content to import the contents of all your selected files
4. To get the Manager/Project detail, edit the "Removed Other Columns" step and add "Folder Path" back in to get your file name

That generates a query similar to this (the red bits are manually edited per steps 2 and 4 above):

let
    Source = Folder.Files("c:\temp"),
    #"Filtered Rows" = Table.SelectRows(Source, each (Text.Contains([Name],"Productivity"))),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows", "Transform Binary from temp", each #"Transform Binary from temp"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Folder Path", "Source.Name", "Transform Binary from temp"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform Binary from temp", Table.ColumnNames(#"Removed Other Columns1"[#"Transform Binary from temp"]{0}))
in
    #"Expanded Table Column1"

and generates a table like this:

Then you can get the Manager and Project by parsing the Folder Path column.

In terms of an access control list, again I'm not sure how Clients are derived for Projects and Managers.  You may need to consider Row Level Security to do that properly, or create different versions of the PBIX for each client held in each Manager folder etc.

If the data volumes become an issue, you may need to consider conditional refresh of old (combined?, history?) files per @MattAllington - see http://exceleratorbi.com.au/conditionally-refresh-queries-power-bi/

[rhytha]

Thank you steve for explanation.

There is not much difference between the productivity reports or other reports across the projects. They might have some extra columns in the excel file, but most will be the same.

The clients will access the dashboard published via Powerbi.com cloud using the user credentials we will be creating and giving them. The client access is based on the Project, for example if Client 1 will have access to Project 1 reports, if a client has more than 1 projects, he will have access to that as well.

Thanks for the link on history, refresh, i went through it. There are few manual process involved in that method, since we have nearly ~70 projects and excel files run into 2 GB usually is there a better method available?. Will importing this to a SQL or mySQL Db will make the refresh faster, smoother etc, I can import the excel files into a azure mysql DB. 

But will Power Apps (once the dashboard is published) a refresh in full

[Anonymous · Accepted solution]

@rhytha,

If the files have different structures, you may get errors or strange results if you import them all in one go.  And I assume they hold different data?  I suggest you check that carefully, or even duplicate the import for each file type separately.

If you are publishing via powerbi.com, I think you'll need to use Row Level Security  (RLS) to enforce access control for specific users, or replicate your dashboard and publish separately for each user group, against specific filtered datasets.  There are various good posts on RLS - e.g. http://radacad.com/row-level-security-configuration-in-power-bi-desktop

On performance, it's too hard to say at this stage.  I recommend getting your basic model set up first then adapt as required once you have tested for performance issues.