Slicer to select specific SharePoint Folder

[Anonymous]

Hello,

We have a SharePoint site that stores time keeping reports.  At the end of the month, I have configured a flow to move the current report to an archive folder for the month.  'January', 'Febuary', 'March', etc... are the folder names.  I am wondering if it is possible to create a slicer in Power BI to allow a user to select what folder they want to view containing that months report.  The visualiations would then all update accordingly.  I was thinking it could be possible by writing some Python to pull the date that is located on the report, then adding a column full of this data.  Then I could combine all reports and slice based off of that.  

Wondering if anyone else had any better ideas.

Thanks,

Brad

[dax]

Hi @Anonymous ,

If you want to use slicer to achieve this goal, you need to make sure all columns in each month folder have same name and type. Then you could try to below code to achieve this goal

let
    Source = SharePoint.Files("https://<company>.sharepoint.com/teams/fortest2", [ApiVersion = 15]),
    #"Inserted Text Between Delimiters" = Table.AddColumn(Source, "Text Between Delimiters", each Text.BetweenDelimiters([Folder Path], "/", "/", {1, RelativePosition.FromEnd}, 0), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Text Between Delimiters", each ([Extension] = ".xlsx") and ([Text Between Delimiters] = "Feb" or [Text Between Delimiters] = "Jan")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Text Between Delimiters", "Transform File (2)"}),
    #"Expanded Transform File (2)" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", {"year", "max", "date"}, {"year", "max", "date"})
in
    #"Expanded Transform File (2)"

when you click expand [content], it will automatically generate #Invoke Custom Function1 step, then you could choose [Transform File] and month column to expand, and use this month column in slicer, then you will achieve your goal.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , I doubt you can change the connection setting at run time. But check if this can help

https://community.powerbi.com/t5/Power-Query/Connecting-to-Sharepoint-Specific-Folder/m-p/876092

[parry2k · Accepted solution]

@Anonymous the technique should be load all the reports and tag each report with a respective period which will be then used to slicer the data. There are many topics on how to load data from share point folders and append together.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!