Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Yes Please, Im looking for solution
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?
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
(((((#### 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
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)
I cant open the file, it says either the file is corrupted or encrypted
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 )
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:
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
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
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 )
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
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |