Transform Rolling Weeks Data To Only Filter To Max Date Per Source File

[dswinden]

Hi Friends!

I know there are better ways to do this, but I am limited to my current methodology unless you have a better idea?

Summary of problem.  My data vendor sends me a file every week that has the last 4 weeks of sales history in that file by week.  So this week for example, on Monday May 13 would receive;

Sales Week Starting;

April 15

April 22

April 29

May 6

I am storing my data in a sharepoint folder and trying to transform the data from that folder using sharepoint folder in power query.  The problem I run into as you can imagine is that every week would quadruple the sales of that week, since i receive the data for every week four times.  

I am trying to figure out a way to apply logic in the power query to filter to the MAX date per source file name.  For example in below screen shot under yellow the "Date File Received" would be the source file name, and "Week" would be the week that sales represents.  On the right under Green columns is what the values I want to be left.

So for example looking at the data received on March 4th, this includes weeks of Feb 12, Feb 19, Feb 26, and March 4 (744, 812, 906, 885 for a total sum of 3347).  However, the actual sales for the week of March 4th are only 885 units and that is the only number i want left to be loaded to my dataset. 

Please help? 🙂

[Ritaf1983 · Accepted solution]

Hi @dswinden 
you can add a conditional column that compares the week with the received :

and filter out all those rows wich null :

Result (the column test can be deleted)

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

maybe you can try this

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBCsUgDAXv4rqgpkkbz1K8/zV++ApN2hC7Gxgd5V1XggKY94xpGwi5gvCJmPrm6ibMcsjXcAi3crh6IDNpW2sc196r336Rn0zyiPb8ylff+3n+mp/8yAOFea29vPGL/J8Z+faYw3GsftWNDuMDWzOXw2GM9tr8rT2QCpp/rUahMK51WJ/MZ0m9/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data file received" = _t, week = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data file received", type date}, {"week", type date}, {"Sales", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Data file received] =List.Min(#"Changed Type"[#"Data file received"]) or [Data file received]= [week] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"

pls see the attachment below

[dswinden]

Hi @ryan_mayu Thank you for your reply!  I attempted that and get this error.  I wonder if it is due to the fact that i am first transforming the file from a sharepoint instead of having the source data directly in pbix?

"Formula.Firewall: Query 'BC Distribution Data' (step 'Change File Received Date') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

[ryan_mayu]

what about create a duplicated column and change the Change File Received Date to Change File Received Date - Copy?

[Ritaf1983 · Accepted solution]

Hi @dswinden 
you can add a conditional column that compares the week with the received :

and filter out all those rows wich null :

Result (the column test can be deleted)

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[dswinden]

Thanks Rita, this solved 99% of my issue, the other 1% is just the very first week of data 

So file received on say October 2 will have the weeks of Sept 4, Sept 11, Sept 18, Sept 25 in it.  This process just eliminates the Sept 4, 11, 18 weeks from my sales history, but everything Sept 25th onwards is perfect.  I will solve by just creating csv's for those three weeks seperately with the source file dates matching. 

Thank you so much!

[Ritaf1983]

Happy to help 🙂