Solved: DATA from SharePoint

faheem_Latif_BI · ‎11-06-2022

Hi life savers,

I have 2 excel files on Sharepoint, every month our client put latest file for current month.

Now the question is for latest file i consider is as CURRENT table , and for very immidiate next file would be my prevoius file.

e.g

Pensions_PowerBI_data_102022.xls (OCT FILE) --would be considered as previous file in power BI

Pensions_PowerBI_data_112022.xls (NOV FILE) --would be considered as current file in power BI

for next month:

Pensions_PowerBI_data_112022.xls (OCT FILE) --would be considered as previous file in power BI

Pensions_PowerBI_data_122022.xls (NOV FILE) --would be considered as current file in power BI

how i build this logic in power query.

Thanks

ImkeF · ‎11-06-2022

Hi @faheem_Latif_BI
if you use the SharepointFolder connector, you first sort the files by creation date in descending order and then select the first 2 rows.
After that, create an index column and use the different numbers generated there to determine current (0) and previous month (1).

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

ImkeF · ‎11-06-2022

Hi @faheem_Latif_BI
if you use the SharepointFolder connector, you first sort the files by creation date in descending order and then select the first 2 rows.
After that, create an index column and use the different numbers generated there to determine current (0) and previous month (1).

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries