CODEWITHSUNDEEP
powerquery
LuBo
LuBo

Reputation: 5

Monthly Sales to Weekly sales - calculation by full period

I am blocked to set 3 calculations to get weekly sales report from a 4 weeks period sales report.

Context: i merge different Sales reports, add a date table based on Customer Fiscal Year. Sales informations are monthly and i need to transform them on a weekly base. A period is composed of 4 weeks and we received each week a report where sales are added. So, i need to check if the 4 reports of the period are loaded to be able to deduce Week 1+2+3 from full Period, then Sales of week 1+2 from sales Week 1+2+3, and Sales of week 1 from sales week 1+2.... Final is to get sales of each week

I am blocked on the step: When the Full 4 week period is available (="OUI"), for each Period and product (UPC), i want to deduce sales and margin for week 1+2+3 from 1+2+3+4 and then to 1+2 from 1+2+3 and 1 from 1+2, to obtain seperated sales.

I created a unique ID to help: column "UPC_YEAR-PERIOD-MOD" where MOD is the week number place (-1,-2,-3 or -4) within a period (13 period of 4 weeks). Visual is this one:

enter image description here

My actual calculation code with the last wrong step is:

let
Source = Folder.Files("T:\Business\@Automated Data\POS Kro-PM\@Mailed Raw Reports"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform Sample File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform Sample File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform Sample File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Table Column1",{"Column10", "Source.Name", "XXXX", "Column2", "Column4", "Column5"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Source.Name", "XXXX", "Column2", "Column4", "Column5", "Column10"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns", "Source.Name", "Source.Name - Copy"),
#"Reordered Columns1" = Table.ReorderColumns(#"Duplicated Column",{"Source.Name", "Source.Name - Copy", "XXXX", "Column2", "Column4", "Column5", "Column10"}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Reordered Columns1", {{"Column2", each Text.AfterDelimiter(_, "-"), type text}}),
#"Removed Blank Rows" = Table.SelectRows(#"Extracted Text After Delimiter", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Removed Top Rows" = Table.Skip(#"Removed Blank Rows",9),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([#""] <> "")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"2022 XXXX Report.xlsx", "Raw file name"}, {"2022 XXXX Report.xlsx_1", "End week date"}, {"", "Description"}}),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Renamed Columns", {{"End week date", each Text.BetweenDelimiters(_, "WE", "dtd"), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Between Delimiters",{{"Raw file name", type text}, {"End week date", type date}, {"UPC", Int64.Type}, {"Scanned Retail $ - CURRENT", Currency.Type}, {"Scanned Retail $ - PREV", Currency.Type}, {"Gross Margin $ - CURRENT", Currency.Type}}),
#"Added Custom Column" = Table.AddColumn(#"Changed Type", "New file name", each let splitEndweekdate = Splitter.SplitTextByDelimiter("-", QuoteStyle.None)(Text.From([End week date], "fr-CA")), splitRawfilename = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Raw file name]) in Text.Combine({"FM_", Text.Combine(splitEndweekdate), Text.Middle(splitRawfilename{0}?, 7, 8)}), type text),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Custom Column",{"Raw file name", "End week date", "New file name", "UPC", "Description", "Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns2",{"Raw file name", "End week date"}),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Removed Columns", "Text After Delimiter", each Text.AfterDelimiter([New file name], "_"), type text),
#"Renamed Columns3" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", "Aide Modulo"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Aide Modulo", type date}}),
#"Inserted Week of Year" = Table.AddColumn(#"Changed Type1", "Week of Year", each Date.WeekOfYear([Aide Modulo]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Week of Year", "Month", each Date.Month([Aide Modulo]), Int64.Type),
#"Renamed Columns4" = Table.RenameColumns(#"Inserted Month",{{"Week of Year", "XXXX Year week"}}),
#"Reordered Columns3" = Table.ReorderColumns(#"Renamed Columns4",{"New file name", "XXXX Year week", "Aide Modulo", "UPC", "Description", "Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT"}),
#"Reordered Columns4" = Table.ReorderColumns(#"Reordered Columns3",{"New file name", "Aide Modulo", "XXXX Year week", "Month", "UPC", "Description", "Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns4",{{"Aide Modulo", Order.Ascending}}),
#"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Aide Modulo"}, #"FM Fiscal Calendar", {"DateKey"}, "FM Fiscal Calendar", JoinKind.FullOuter),
#"Expanded FM Fiscal Calendar" = Table.ExpandTableColumn(#"Merged Queries", "FM Fiscal Calendar", {"FY Year", "FY Period", "FY Week", "FY Week Modulo", "FY Period Week Modulo"}, {"FM Fiscal Calendar.FY Year", "FM Fiscal Calendar.FY Period", "FM Fiscal Calendar.FY Week", "FM Fiscal Calendar.FY Week Modulo", "FM Fiscal Calendar.FY Period Week Modulo"}),
#"Reordered Columns5" = Table.ReorderColumns(#"Expanded FM Fiscal Calendar",{"New file name", "Aide Modulo", "XXXX Year week", "FM Fiscal Calendar.FY Week", "FM Fiscal Calendar.FY Week Modulo", "FM Fiscal Calendar.FY Period Week Modulo", "Month", "FM Fiscal Calendar.FY Year", "FM Fiscal Calendar.FY Period", "UPC", "Description", "Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT"}),
#"Supp ""null"" to New file name" = Table.SelectRows(#"Reordered Columns5", each ([New file name] <> null)),
#"Creation Col Fm FIscal FY Y+P" = Table.AddColumn(#"Supp ""null"" to New file name", "FM Fiscal FY Y+P", each Text.Combine({Text.From([FM Fiscal Calendar.FY Year], "fr-CA"), "-", Text.From([FM Fiscal Calendar.FY Period], "fr-CA")}), type text),
#"Reordered Columns6" = Table.ReorderColumns(#"Creation Col Fm FIscal FY Y+P",{"New file name", "Aide Modulo", "XXXX Year week", "FM Fiscal Calendar.FY Week", "FM Fiscal Calendar.FY Week Modulo", "FM Fiscal Calendar.FY Period Week Modulo", "Month", "FM Fiscal Calendar.FY Year", "FM Fiscal Calendar.FY Period", "FM Fiscal FY Y+P", "UPC", "Description", "Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT"}),
#"UPC_YEAR-PERIOD-MOD" = Table.AddColumn(#"Reordered Columns6", "UPC_YEAR-PERIOD-MOD", each Text.Combine({Text.From([UPC], "fr-CA"), "_", [FM Fiscal Calendar.FY Period Week Modulo]}), type text),
#"Calcul Full Period Check" = Table.AddColumn(#"UPC_YEAR-PERIOD-MOD", "Full Period YN", each let
Source = #"UPC_YEAR-PERIOD-MOD",
#"Sub Grouped Rows" = Table.Group(Source, {"FM Fiscal FY Y+P"}, {{"Count", each List.Count(List.Distinct(List.Transform([FM Fiscal Calendar.FY Week Modulo], each Number.Mod(_, 4))))}}),
#"Sub Added Custom" = Table.AddColumn(#"Sub Grouped Rows", "Full Period Check", each if [Count] = 4 then "OUI" else "NON")
in
#"Sub Added Custom"),
#"Merged Queries1" = Table.NestedJoin(#"Calcul Full Period Check", {"FM Fiscal FY Y+P"}, #"Full Period YN", {"FM Fiscal FY Y+P"}, "Full Period YN.1", JoinKind.LeftOuter),
#"Expanded Full Period YN.1" = Table.ExpandTableColumn(#"Merged Queries1", "Full Period YN.1", {"Full Period Check"}, {"Full Period YN.1.Full Period Check"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Full Period YN.1",{"Aide Modulo", "FM Fiscal Calendar.FY Week Modulo", "FM Fiscal Calendar.FY Period Week Modulo", "Month", "FM Fiscal Calendar.FY Year", "FM Fiscal Calendar.FY Period", "FM Fiscal FY Y+P", "Full Period YN"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Full Period YN.1.Full Period Check", "Full Period CheckBox"}}),

  #"Added Custom" = Table.AddColumn(#"Renamed Columns2", "Scanned Retail $CURRENT - Sem 4", each let
Source de données
Source = #"Renamed Columns2", // Remplacez YourDataSource par le nom de votre source de données

FiltreOui = Table.SelectRows(Source, each [#"Full Period CheckBox"] = "OUI"),

CalculDifference = Table.AddColumn(
FiltreOui,
# #         "Scanned Retail $ - Sem 4",
each
let
UPC_Year_Period_MOD = [#"UPC_YEAR-PERIOD-MOD"]
in
try

let
yearMinus4 = Number.FromText(Text.End(UPC_Year_Period_MOD, 2)) - 4,
yearMinus3 = Number.FromText(Text.End(UPC_Year_Period_MOD, 2)) - 3
in
Calcul et ajout du résultat dans la nouvelle colonne
[#"Scanned Retail $ - CURRENT"]{[#"UPC_YEAR-PERIOD-MOD" = Text.Start(UPC_Year_Period_MOD, Text.Length(UPC_Year_Period_MOD) - 2) & Text.From(yearMinus4)]} -
[#"Scanned Retail $ - CURRENT"]{[#"UPC_YEAR-PERIOD-MOD" = Text.Start(UPC_Year_Period_MOD, Text.Length(UPC_Year_Period_MOD) - 2) & Text.From(yearMinus3)]}
otherwise null

in
CalculDifference)*
in   #"Added Custom"

Resolve this step:

When the Full 4 week period is available (="OUI"), for each Period and product (UPC), i want to deduce sales and margin for week 1+2+3 from 1+2+3+4 and then to 1+2 from 1+2+3 and 1 from 1+2, to obtain seperated sales.

Upvotes: 0

Views: 91

Answers (2)

Sam Nseir
Sam Nseir

Reputation: 12111

If I understood your question correctly - your data is a running total and you want to split it up by week. So that:

Week 1 = MOD 1
Week 2 = MOD 2 - MOD 1
Week 3 = MOD 3 - MOD 2
Week 4 = MOD 4 - MOD 3

Try the following.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "UPC_YEAR-PERIOD-MOD", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"UPC_YEAR-PERIOD", "MOD"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Scanned Retail $ - CURRENT", Int64.Type}, {"Scanned Retail $ - PREV", Int64.Type}, {"Gross Margin $ - CURRENT", type number}, {"MOD", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"UPC", "Description", "UPC_YEAR-PERIOD"}, {{"Rows", each
      let gpRows = _,
      Add1 = Table.AddColumn(gpRows, "WK Sales $ - CURRENT", each if [Full Period CheckBox] <> "OUI" then null else if [MOD] = 1 then [#"Scanned Retail $ - CURRENT"] else [#"Scanned Retail $ - CURRENT"] - gpRows{[MOD]-2}[#"Scanned Retail $ - CURRENT"], Int64.Type),
      Add2 = Table.AddColumn(Add1, "WK Sales $ - PREV", each if [Full Period CheckBox] <> "OUI" then null else if [MOD] = 1 then [#"Scanned Retail $ - PREV"] else [#"Scanned Retail $ - PREV"] - gpRows{[MOD]-2}[#"Scanned Retail $ - PREV"], Int64.Type),
      Add3 = Table.AddColumn(Add2, "WK Margin $ - CURRENT", each if [Full Period CheckBox] <> "OUI" then null else if [MOD] = 1 then [#"Gross Margin $ - CURRENT"] else [#"Gross Margin $ - CURRENT"] - gpRows{[MOD]-2}[#"Gross Margin $ - CURRENT"], Int64.Type)
      in Add3
    , type table}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT", "MOD", "Full Period CheckBox", "WK Sales $ - CURRENT", "WK Sales $ - PREV", "WK Margin $ - CURRENT"}, {"Scanned Retail $ - CURRENT", "Scanned Retail $ - PREV", "Gross Margin $ - CURRENT", "MOD", "Full Period CheckBox", "WK Sales $ - CURRENT", "WK Sales $ - PREV", "WK Margin $ - CURRENT"})
in
    #"Expanded Rows"

enter image description here

Upvotes: 0

horseyride
horseyride

Reputation: 21373

See if this helps you at all. If there are 4 periods [grouped by UPS and Period] it calculates sum of column for periods 1+2+3+4, 1+2+3 and 1+2

let   Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"UPC", "Period"}, {{"data", each 
    let base= _,
    #"Added Custom" = Table.AddColumn(base , "AAA_1234", each if Table.RowCount(base)=4 then base[AAA]{0}+base [AAA]{1}+base [AAA]{2}+base [AAA]{3} else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom" , "AAA_123", each if Table.RowCount(base)=4 then base[AAA]{0}+base [AAA]{1}+base [AAA]{2} else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2" , "AAA_12", each if Table.RowCount(base)=4 then base[AAA]{0}+base [AAA]{1} else null)
in   #"Added Custom3", type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"AAA", "BBB", "CCC", "AAA_1234", "AAA_123", "AAA_12"}, {"AAA", "BBB", "CCC", "AAA_1234", "AAA_123", "AAA_12"})
in     #"Expanded data"

enter image description here

ALTERNATE

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"UPC", "Period"}, {{"data", each 
        let base= _,
        output= if Table.RowCount(base)<>4 then Table.FromRecords({ [AAA_1234 = null, AAA_123 = null, AAA_12 = null,  AAA_1 = null]}) 
        else Table.FromRecords({
            [AAA_1234 = base[AAA]{0}+base [AAA]{1}+base [AAA]{2}+base [AAA]{3},
            AAA_123 = base[AAA]{0}+base [AAA]{1}+base [AAA]{2},
            AAA_12 = base[AAA]{0}+base [AAA]{1},
            AAA_1 = base[AAA]{0} ]})
    in output, type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"AAA_1234", "AAA_123", "AAA_12", "AAA_1"}, {"AAA_1234", "AAA_123", "AAA_12", "AAA_1"})
in  #"Expanded data"

enter image description here

Upvotes: 0

Related Questions