Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FU
Helper IV
Helper IV

[Unsolved] Showing historic snapshot reports (urgent)

Hi all,

I am looking for some support in visualisng my monthly snapshot reports that are connected to my SharePoint folder. 

I have provided a set of snapshot Excel files and I am seeking someone's help to pull this together into Power BI and visualise it in this way (and if possible sending back the pbix file for reference)

I am looking to visualise this roughly (but open to suggestions) in the format below:

1) A date slicer so that I can filter between any 2 days and see the changes that have occured (Snapshots taken once a month)

2) Be able to filter by 'Portfolio Name' 'Programme Name' 'Project Name' using the filters on the left (approx 80% of data is linked to Projects)
2.1) 'Portfolio Name' can be found in the Portfolios table. 'Programme Name' can be found in the programmes table. 'Project Name' can be found in the overview table. 

3) Filter by the table name using the filters on the bottom left of the page (these are also the tables shown in the Excel files linked)
Desired format.png
My Excel files are captured in SharePoint folder in the format below:
Data SharePoint.png
These files can be found here: https://we.tl/t-MooX7SGKRV

I have changed the data in each file so that the changes can be visualised for demonstration purposes.

Would anyone be helpful enough and visualise this for me?
If anyone has any better ideas to show historic data using a date slicer please also drop your suggestions!

This work is quite urgent for me so would appreciate any fast answerers 🙂
If you need any further clarification please let me know here or DM.
Thanks!

7 REPLIES 7
FU
Helper IV
Helper IV

Still unsolved

lbendlin
Super User
Super User

 

A date slicer so that I can filter between any 2 days

 

That means you need to get the file dates somehow, either as part of the file name (preferred)  or using the Created Date meta data (ok-ish) or the Modified Date (questionable)

Filter by the table name 

This means you need as many Power Query queries as you have sheets/tabs/tables you want to compare.  Comparison would be between the snapshots of each sheet/tab/table,  not across the entire Excel file.  If you want data model level snapshot comparisons then you will have to define the measures that should drive that.

I have queries for each table I've imported with "date created" columns. 

 

How can I also get my (project, programme, portfolio) filters to link to my appendid query with all the sheets

you need to run separate queries, one per sheet name.

I have done that and appendid the queries into 1. How do I link Portfolio programme and Project so that I can use them as filters?

I have done that and appendid the queries into 1

I don't think you want to append them as the sheets have different formats. Each comparison will have to be tailored to the sheet structure.

Once I imported the sheets I added an index an un pivoted other columns. Then I appendid so they all had (source name, index, Value, table name) that way I could combine all in one 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors