How to visualize Version history that is available in SharePoint HELPPP MEEEE

[Anonymous]

My requirement : Im trying to show similar funtion that sharepoint allow us by using version history 
Question : Lets say you have a table ( sharpoint list source) you have 10 columns, I have modified 5 columns ,, there fore in sharepoint we get to see version 2.0 whare it show us what are the columns got modified and what is the updated data 
 Similar way If my client select a perticular Date and ID  He should be able to see what changes happened to that record ( how many columns are modified and what are the changes ) 
Below pic is from sharepoint, Trying to Achive IT 

 

some approach I thought
 
IN the above Pic, I have different version on same ID , 703 1.0, 2.0 ,3.0 , 4.0  OFFER status column has PENDING FOR JOINEE and IN-PROCESS , here the update has been made there fore the version created 
IS there a way I can write DAX to check a condition Filter Table using pevious version and check each column with the current version , ( 703 ID check with 2.0 version and check with 3.0 version ) if there is a change in value display the column and the updated value , If it is same, that mean no change on that field so show as BLANK 
 
AIM :- when I click 703 ID with 3.0 version,  I see only OFFERSTATUS and value as IN_PROCESS as it is updated ,, rest columns as blank or it is best if my fuction return a table only with the columns that got updated ( values are not same compare to old version ) 

PLEASE HELP ME  @AllisonKennedy 

[AllisonKennedy]

@Anonymous  Apologies for the delay - I see this isn't solved yet. Are you still looking for a solution? I haven't read all the history, thought I'd check before I spend too much time on it. 

[Anonymous]

Yes Please, Im looking for solution 

[sevenhills]

It is hard to understand.

1) try to build what you need with one list and one sharepoint url

        The link I posted gives the version history, which I have used before. hence shared the link.

        Are you able to come to this step?

2) later convert as parameters 

        Add parameters and replace where you need dynamic

3) You are saying some columns are showing as nulls, which is not possible, unless I see the data

        Can you create some mock data and publish, so that someone also looks into it?

[Anonymous]

1 . created Parameters 
2 created Blank query (Base query) copy pasted the code, it is taking parameters (fine ) 
3 navigated to ADD COLUMN - INVOKE CUSTOM FUNCTION created using the code which fetch all version history 
   Invoke funtion gave 3 fields to fill 
     here im unable to set parameters, for 1 field , I made as column ITEMS.ID , remaining 2 , location and name of list I have to enter manually so I did 

Unable to do schedule refresh 

[Anonymous · Accepted solution]

(((((#### let
    Source = (VersionsRelevantSharePointListName as text, VersionsRelevantSharePointLocation as text, VersionsRelevantItemID as number) => let
        Source = Xml.Tables(Web.Contents(
    "https://YourAddress.sharepoint.com/sites/",
    [RelativePath = VersionsRelevantSharePointLocation & "/_api/web/Lists/getbytitle('" & VersionsRelevantSharePointListName & "')/items(" & Text.From(VersionsRelevantItemID) & ")/versions"]
    )),
        entry = Source{0}[entry],
        #"Removed Other Columns2" = Table.SelectColumns(entry,{"content"}),
        #"Expanded content" = Table.ExpandTableColumn(#"Removed Other Columns2", "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"content"}),
        #"Expanded content1" = Table.ExpandTableColumn(#"Expanded content", "content", {"properties"}, {"properties"}),
        #"Expanded properties" = Table.ExpandTableColumn(#"Expanded content1", "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"properties"})
    in
        #"Expanded properties"
in
    Source #####)))))

SOLUTION CUSTOM FUN() FOR SCHEDULE REFRESH

[sevenhills]

In my view, first get the report and model working per your needs. Second is the refresh.

Can you post your .pbix file to look?
(Remove all sensitive info and identifiers)

[Anonymous]

https://drive.google.com/file/d/1BBwprtL3wca-A6-YULVmOJLimaSTvZnI/view?usp=drivesdk  Thats is what i did 

[sevenhills]

I cant open the file, it says either the file is corrupted or encrypted

[Anonymous]

Ya Im sorry, My work PC is encrypted, looks like I cant upload file, outside my Organisation 

I can post all screen shorts what I did 

Im able to get data and create reports, how ever - as datasource become dynamic, Im unable to do schedule refresh on it 

also Im facing problem in creating TIMELINE report ( I explained in actual request thread ) 
data is fine but I dont know how to do it ( Need to show a table which visualize dynamic colums based on Time line history or date )

[sevenhills]

Sorry, for me it is tough to say what is wrong.

Gist of it, let us separate as three parts

1) Dynamicness of URL AKA data sources and scheduled refresh

Definitely, there are issues and some indirect/direct solutons exists, please check these urls:

• https://blog.jongallant.com/2021/08/powerbi-dynamic-data-source-refresh/
• https://medium.datadriveninvestor.com/setting-a-scheduled-refresh-on-a-dynamic-data-source-in-power-... 
• https://community.powerbi.com/t5/Power-Query/Dynamic-data-sources-aren-t-refreshed-in-the-Power-BI-s...
• https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power... 
• https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i... 

If these solutions do not fit, it is kind of tough.

2) Data prep - which it seems to be working from your post

3) Timeline visual

In my view this is easy when compared to scheduled refresh, but not sure where you got stuck. 

Please take a look at these articles:

https://www.wallstreetmojo.com/power-bi-timeline/ 

https://blog.pragmaticworks.com/power-bi-custom-visuals-as-timeline

[Anonymous]

Thank You for your response 🙂 

1 Data Prep,  I was able to import data from sharpoint list, along with the versions , There for able to build reports
 2 May be its not Time line report , my requirement is  

That is from sharepoint, if we select and ID and navigate to check version history of that ID it will show us ( what are the versions available and based on version what are the modifications done on complete table )  { out of comple table " that (sharepoint version history functionality ) display us only those colums got effected and what is the modified data " } 
Im trying to obtain that in power BI 
Here I have complete table imported along with versions, How ever If i want to display a table or some visual which show me ( if I select an Id and date ) what are the modifications hapend on that compare with previous version or previous state 
What i was able to achive is , I display a table with 4,5 fixed colums and as it contain all the data ( fixed 4,5 colums ) as I  select a date and id, table gets filtered to display all 4,5 colums and data insted of those colums should be display which are modified { this kind of dynamic display of columns based on user input ( is it posible in power BI ) } 

3 :- Dynamic data - Scedule refresh, I did try what i can ( setting relative path and query ) passing parameters, differt ways, The blog link you have posted- it can obtain the solution 
Im unable to write proper code for it - Please Help me 

can you please show me how can I set [Relative Path] and [ Query ] for the below fuction 

let
Source = (VersionsRelevantSharePointListName as text, VersionsRelevantSharePointLocation as text, VersionsRelevantItemID as number) => let

Source = Xml.Tables(Web.Contents(Text.Combine({

VersionsRelevantSharePointLocation,

"/_api/web/Lists/getbytitle('",

VersionsRelevantSharePointListName ,

"')/items(",

Text.From(VersionsRelevantItemID),

")/versions"}

))),

entry = Source{0}[entry],

#"Removed Other Columns2" = Table.SelectColumns(entry,{"content"}),

#"Expanded content" = Table.ExpandTableColumn(#"Removed Other Columns2", "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"content"}),

#"Expanded content1" = Table.ExpandTableColumn(#"Expanded content", "content", {"properties"}, {"properties"}),

#"Expanded properties" = Table.ExpandTableColumn(#"Expanded content1", "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"properties"})

in

#"Expanded properties"
in
Source 

Please help me with the code part. Thank You 

[Anonymous]

Above is the sample PBXI file, Thank You.. 

How ever , As per the request thread I have raised , IM trying to create a Timeline visual which should show me a table based on time selected 
table should contain dynamic colums , based on ID selected I should be seeing those columns which got modified comparing to previuos data 

Example : ets say you have a table ( sharpoint list source) you have 10 columns, I have modified 5 columns ,, there fore in sharepoint we get to see version 2.0 whare it show us what are the columns got modified and what is the updated data 
 Similar way If my client select a perticular Date and ID  He should be able to see what changes happened to that record ( how many columns are modified and what are the changes ) 

[Anonymous]

Yes with the help of that I'm able to load the versions 

how's ever as we are using custom function 

datasourse become

dynamic 

I'm unable to do schedule refresh on dynamic data sources 

for that to solve I have to write some relative path and set query in m-code 

im weak in code 

please if you could help me I'm very thankful 

I used same above fiction to pull version data 

HOWEVER my query here is 

I have to build a time like report (visual) 

which should show version wise , what are the column  got effected and what modification has done based on date and ID that client selected 

I have described and attached a pic to above query 

looking for similar result 

Thanks a lot 

[sevenhills]

will this help?

https://community.powerbi.com/t5/Community-Blog/How-to-get-version-history-from-SharePoint/ba-p/1956...