Forum Discussion
Last year sales Column in Query Editor
Hi All, How to calculate last year sales for same time period in Query editor?I need to use Unpivot option hence i need to claculate those in Edit query. Thanks in Advance! Month Sale...
Hi Anonymous ,
It need to merge tables. Please try like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc/JDYBADEPRXnJGIsnstYzovw0EDgq+Pvngv7fYaaerLTnEVOU6tniSB5WkElSTalBLakE9qQeNpBE0k2bQSlpBpvQ18B/wFdgvwb4GDF0fK5QKq9QKaxQL61QLG5QLm9QLWxT8misVw4yTgc7JwMLJwDflugE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Sale = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Sale", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddYears([Month],-1)), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Added Custom", {"Month"}, "Added Custom", JoinKind.LeftOuter), #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Sale"}, {"Sale.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"Custom"}) in #"Removed Columns"
It's need to be crated in Query editor with M lang., So that I can use Unpivot Propery for few columns.
Hi Anonymous ,
It need to merge tables. Please try like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc/JDYBADEPRXnJGIsnstYzovw0EDgq+Pvngv7fYaaerLTnEVOU6tniSB5WkElSTalBLakE9qQeNpBE0k2bQSlpBpvQ18B/wFdgvwb4GDF0fK5QKq9QKaxQL61QLG5QLm9QLWxT8misVw4yTgc7JwMLJwDflugE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Sale = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Sale", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddYears([Month],-1)), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Added Custom", {"Month"}, "Added Custom", JoinKind.LeftOuter), #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Sale"}, {"Sale.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"Custom"}) in #"Removed Columns"Did you get a solution?
