Function to Filter Sharepoint files from Folder

[jjkrmr]

I am hoping someone could determine if this is even possible? I am given excel files that have certain date ranges based on worker information. The date ranges in the files will overlap because sometimes the content changes or has to be approved etc. Ultimately, we could list the name of the file and filter the content to only include the date ranges where it has not been covered from a previous file with a StartDate and EndDateParameters that are specific to each file. Each week a new file will be added to follow this pattern.

The function would use the from Sharepoint Folder connector and transform the contents based on the output of each files start/end date.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZm9kiUnDIVfZWridV8kfhryfQKnWxts4MxVdtkb7OO7mws6h7bISPorPu4RSDPfvn3+/sfff/3z8+Prj58/Pn77CO0I+dD4Xmq5lsevP//99fnls73yS4PGvtQy1+Hz+5f/Ueqh1ShSiVJfWoERW8sGo8DoilHC2Fo3GAFGVowQxtbRx0gyTEgLRhIwtkw+JZyDMk4JlHN+yaeUPcp5RAGFlc5XFMLYuvgYTYaRtGA0AQO908cInLoeMEJSWG4oYVCGHlGCbYD8mo8J0TC6OkVQoCduhMs7/aQ3MYXSz37iZrgc0sAJC0caccxQ3BCXdwGQIXGUTscwbogL6rus9V1Q32woboovRjNMF5yYC9LAgaG4Oc6o8LJWeKYKZ0Nxg5wPgVY3BEfIy5biBjkfYd5bwxAYFCQZipvkCyPArFZCGEi5SU6ozuE3MYniy4LqRjmhPIcgOIgvGaqb5IT6HIKECbQdw7hBTqjPtNZnQnhZUN0gX4zTMEt9XpATHAiqm+SLE8AJKycQx9bqJjkeAq2lPuNLyMuW6gY5HmFeO8MQGKtJNlQ3yIrTGYITo/QtG6qbZMXpDEHiBHBgqG6UFaczDMHBt6zoJllxOrqejuLTxdBNshwxAMOnI68YiANDN8q9BiZnOZ2rBnAibOhmWQ6B1nI68hLywum4UZYjzEt5GAKDG4sMo5vkC6OG0YcVMBCMXpLvy/wyUfIzjI73RR+G0cvyBbofGCVDAr0fGF0do5flm6Pg6IOj4Jhk9LJ8QfqtTo7EyYZhyeSl+e5Eq4G6o4FkNID6tExeoG+SgKQPkjDJPJMX6Yt0t4FKnkR6t4H6ME1epi/Q3X4peRLoNA6LJi/V15UcBSBhUBi9oD5Fkxfs+3JPRuqaRNJEJIgmN9uhvzhKnkQStrN1crMd+pOj5MmgYCA2TW66Q390lDz5mAgE0eRe1c3qdmpiAqSyZc/sXtbN6nZqAkRly57Zva6bFe60JJDyGRnHva6bFe6UfMy2+pDMmzlQm2GWsq2jM9SHY95Mgqrg6MpR4tg6u/d17b2hkiHNpeRly+x2HrW3hkqGwFiJsmF2O4/aW0MlQdoNYSC1GQY1G0ZY6kpdBgeCeTMNCqxCXThCWrYsbpbP/ncDJUHCkJYti5vks//dQEkQGCtLFiybWVBPwwhLldH76EOwbIZBDeDIygnEgZab5NKbHyVDmk0jOKZYNsNgnNsZO8PYFG07vLWyGQY1GkfiwtEIDrZWNtOgFOOEsnCkgIPlbhqc98UwBAbFxIZuknPvWZQEyQoYCJ6bYdAKa/jRUIliIsFzNw1W44S2cJA7Mjw306AV1hAkjJCWYdwkJxRWWgsrIXUseO6GwWqYpbAi9ykkeG6GwdmPTUHiCHFsfW6mQbva03q1R7r5Eq72czcNTq24tlBKWry1czcNinGWBkrp20jt07mbBpNxlvZJ6duI5qnupsF5DeraYio+ZcPqJlnxYun6Yinuc6UXq+6GwWyY5cUSyp3S71Z3w2A1zvJiCeVO6U3eDIOzs7yHQVkxaCypr6y7aTAaZ+0rqa2krrLupsHrt5Kxs6VjnvO6vPeGH30zDd4vg5AigwKBTLLuxsFooKWrlDmxy2pZd+NgeXOmJHGKYdiy7cbBBlBgkIy2S56abTcOqpG6JZOUSPBsu3EwG6l78jiYiWSmbTcO1gEangSqxmHR5o6D0qtVSJN3xCDzbLtpMBuoW/I0mIkEz7abBuH2/n8MDXEsZ+u2mwbh1jUZxHK2brthMA3Q0CRQMg57tt0weBpIWK1x1S6aYTMNzrqdmkQKTIJn2I2D0Ujdk8bBSCQSvcL9/T8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, FileStartDate = _t, FileEndDate = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"FileStartDate", type date}, {"FileEndDate", type date}, {"Index", Int64.Type}})
in
    #"Changed Type"

Here is an example of what a file might look like for the content. For this "file", it would need to be filtered to show only 1 row or day because all other days are covered from a more recent file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdDLDcMwDIPhXXwOYJNy/JglyP5rpCggNuxN+HjSf10FjHKUVYnK9r3Pch8vZ/pwj/Tp3tOX+5m+3Uc6mg9TA3xYGujD1hA2RNPQfdDTeH+968+HuWJgmisGlrliYJsrBpu5YhDmakGaKwXDXCXY/S+V4N/D+pifj+8H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Worker = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Worker", Int64.Type}, {"Date", type date}, {"Value", Int64.Type}})
in
    #"Changed Type"

[lbendlin]

It might be simpler to do a full crossjoin and then for each data row pick the "newest"  file.

Maybe provide some more sample data?

[jjkrmr]

Thanks. Let me think about how I could provide some addtional samples of data. I think the real challenge is I will be calling a function to be run against a sharepoint folder and have it recognize this logic some how. So as we expand rows on the actual file it knows that it needs to exclude certain rows.