Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everone,
I am connecting my power bi to sharepoint folder which has following file names
2021_04_12 CIO Tasks Calendar - Week 39- V2.0
2021_04_11 CIO Tasks Calendar - Week 39- V2.0
2021_04_10 CIO Tasks Calendar - Week 39- V2.0
i want to upload the last added file to power bi in above file names the last added file is 2021_04_12 CIO Tasks Calendar - Week 39- V2.0.
As i understood correctly if i make any chages in files in share point then Date modified & Date created chages so can't consider those columns
Solved! Go to Solution.
@Anonymous the Date Created should not change - only the Date modified.
Are these the only files in the folder? You can split the filename by delimiter and sort by date and then keep top 1 row.
Let me know if you want more detail on how to do this in Power Query, but based on your comment about not being able to use date modified column I'm assuming a lot about what you already know. 🙂 And we all know it's a bad idea to make assumptions, so let us know how you get on. 🙂
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
@Anonymous Only the modified date will change, created date will always be the date you first created the file with that name.
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
@Anonymous the Date Created should not change - only the Date modified.
Are these the only files in the folder? You can split the filename by delimiter and sort by date and then keep top 1 row.
Let me know if you want more detail on how to do this in Power Query, but based on your comment about not being able to use date modified column I'm assuming a lot about what you already know. 🙂 And we all know it's a bad idea to make assumptions, so let us know how you get on. 🙂
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
@AllisonKennedy Thanks for the solution currently i am doing the similar way but adding the index column and filtering the index column.
Just want to know any other options
@Anonymous Just be careful using the Index column as you can't guarantee the sort order of the files in SharePoint. When you pull data from SharePoint there's access to lots of columns with 'record' and 'table' data in them - there might be a handy column in there that gives the date you're looking for. I'm still not sure why we can't use the [Created] date?
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
Hi @AllisonKennedy yeah your ryt ther is issue with using index so i have split the file name
2021_04_12 CIO Tasks Calendar - Week 39- V2.0 (example) extracting the date and sorting it and keepting the first row only.
The problem with created date is suppose if i upload the file to share point yesterday (19-April-2021) then created and modified date will be (19-April-2021) but if
i accedentaly open the file today (20-April-2021) in the sharepoint then created and modified date chages to (20-April-2021).
which meand created or modified date is not stable is if we open the file in sharepoint then it changed to that particular date
@Anonymous Only the modified date will change, created date will always be the date you first created the file with that name.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |