Forum Discussion
Transform Rolling Weeks Data To Only Filter To Max Date Per Source File
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.
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
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
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?
