Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dswinden
Helper I
Helper I

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

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? 🙂

 

dswinden_0-1715661866168.png

 

2 ACCEPTED SOLUTIONS
Ritaf1983
Super User
Super User

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

Ritaf1983_0-1715663655027.png

and filter out all those rows wich null :

Ritaf1983_1-1715663716539.png

Result (the column test can be deleted)

Ritaf1983_2-1715663764519.png

pbix is attached

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

 

View solution in original post

ryan_mayu
Super User
Super User

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"

 

11.PNG

 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
ryan_mayu
Super User
Super User

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"

 

11.PNG

 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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."

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Ritaf1983
Super User
Super User

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

Ritaf1983_0-1715663655027.png

and filter out all those rows wich null :

Ritaf1983_1-1715663716539.png

Result (the column test can be deleted)

Ritaf1983_2-1715663764519.png

pbix is attached

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

 

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!

Happy to help 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors