SUMIFs forecasted units against calendar dates - dates are in multiple columns

[hafiyco]

Hi,

I'm encountering an issue in Power Query related to using SUMIFs. My data source provides forecasted sales data for each promotional day (from Day 1 to Day 7), as shown in the screenshot below. How can I simplify the table to only display two columns: Calendar Day and Forecasted Units, similar to how the SUMIF function would work in Excel? 

Grouping won't work for me as the Dates are in separate columns. Any help will be much appreciated!

[v-junyant-msft · Accepted solution]

Hi @hafiyco ,

I'm not sure if I understand you correctly.
Your table structure is not very good. You may need to manually merge columns Day1 and QTY1, columns Day2 and QTY2, and so on until columns Day7 and QTY7 are merged by using "-".

    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Day1", type text}, {"QTY1", type text}}, "en-US"),{"Day1", "QTY1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"1"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Day2", type text}, {"QTY2", type text}}, "en-US"),{"Day2", "QTY2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"2"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Day3", type text}, {"QTY3", type text}}, "en-US"),{"Day3", "QTY3"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"3"),
    #"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns2", {{"Day4", type text}, {"QTY4", type text}}, "en-US"),{"Day4", "QTY4"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"4"),
    #"Merged Columns4" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns3", {{"Day5", type text}, {"QTY5", type text}}, "en-US"),{"Day5", "QTY5"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"5"),
    #"Merged Columns5" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns4", {{"Day6", type text}, {"QTY6", type text}}, "en-US"),{"Day6", "QTY6"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"6"),
    #"Merged Columns6" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns5", {{"Day7", type text}, {"QTY7", type text}}, "en-US"),{"Day7", "QTY7"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"7"),

Then Unpivot the entire table:

Then split the Value column by "-":

Here is the whole M code in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZM7ssMgDEX3kjpzow8/rSXj/W8jIAjiFa+LC+vcsQoOlvx+P9qLX0KSHs8eJaJGTBFzxBKx7siQDmaUQIENCNR74PLF9fzx+cw2at2F3EfhTdRVf39uErRxR/KTFljg3QTyJih9MQyYQqFnObIeOR05H7kcOUQopzmCTHMGi0bQQSXw7IPK5n06Ksj+geoftuYQW29Rg3fJmKW5AX1YfLBliC9oQ5JpIbx5m03VaaM2bRZbX1a3qWhuozDeHDYWMhYuFioWJhYiFh52TKgQYfw00q9fD7JWyBBIGdX3tq943RwiGiIaIhoiGiIaIhoiGiL073NdHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day1 = _t, Day2 = _t, Day3 = _t, Day4 = _t, Day5 = _t, Day6 = _t, Day7 = _t, QTY1 = _t, QTY2 = _t, QTY3 = _t, QTY4 = _t, QTY5 = _t, QTY6 = _t, QTY7 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day1", type date}, {"Day2", type date}, {"Day3", type date}, {"Day4", type date}, {"Day5", type date}, {"Day6", type date}, {"Day7", type date}, {"QTY1", type number}, {"QTY2", type number}, {"QTY3", type number}, {"QTY4", type number}, {"QTY5", type number}, {"QTY6", type number}, {"QTY7", type number}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Day1", type text}, {"QTY1", type text}}, "en-US"),{"Day1", "QTY1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"1"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Day2", type text}, {"QTY2", type text}}, "en-US"),{"Day2", "QTY2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"2"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Day3", type text}, {"QTY3", type text}}, "en-US"),{"Day3", "QTY3"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"3"),
    #"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns2", {{"Day4", type text}, {"QTY4", type text}}, "en-US"),{"Day4", "QTY4"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"4"),
    #"Merged Columns4" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns3", {{"Day5", type text}, {"QTY5", type text}}, "en-US"),{"Day5", "QTY5"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"5"),
    #"Merged Columns5" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns4", {{"Day6", type text}, {"QTY6", type text}}, "en-US"),{"Day6", "QTY6"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"6"),
    #"Merged Columns6" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns5", {{"Day7", type text}, {"QTY7", type text}}, "en-US"),{"Day7", "QTY7"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"7"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns6", {}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type date}, {"Value.2", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Value.1", "Calendar Day"}, {"Value.2", "Forecasted Units"}})
in
    #"Renamed Columns"

Then you can do Group By or other operations on this better structured table.

If I misunderstood you, could you please describe what you need in more detail and, if possible, provide a screenshot of your expected result.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hafiyco]

Hi Dino Firstly, appreciate your guidance on this matter! I acknowledge that the original table wasn't structured in the best way. Original file was showing SKU ID, Promotional Start Date, Promo Date 1 to Promo Date 7 and Forecasted Sales Date 1 to Forecasted Sales Date 7. 

I tried following your steps and managed to get the desired table format (Date and Forecasted Units per date). However, during the combined columns and delimiter steps, the date formats were somehow altered. Original format was DD/MM/YYYY (1st Aug 2024) but output was MM/DD/YYYY (8th Jan 2024) instead. The M-code I'm currently using (through your help and Chatgpt) is as shown below. I've added screenshots as well to make it clearer. Thanks for your time and help on this mate!

Current M-code:

let
Source = SharePoint.Files(aaa),
Custom1 = Table.SelectRows(Source, each ([Folder Path] = aaa)),
#"Filtered Hidden Files1" = Table.SelectRows(Custom1, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (9)", each #"Transform File (9)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (9)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (9)", Table.ColumnNames(#"Transform File (9)"(#"Sample File (9)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type text}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type text}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type text}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type any}, {"Column67", type any}, {"Column68", type any}, {"Column69", type any}, {"Column70", type any}, {"Column71", type any}, {"Column72", type any}, {"Column73", type any}, {"Column74", type any}, {"Column75", type any}, {"Column76", type any}, {"Column77", type any}, {"Column78", type any}, {"Column79", type any}, {"Column80", type any}, {"Column81", type any}, {"Column82", type any}, {"Column83", type any}, {"Column84", type any}, {"Column85", type any}, {"Column86", type any}, {"Column87", type text}, {"Column88", type any}, {"Column89", type any}, {"Column90", type any}, {"Column91", type any}, {"Column92", type any}, {"Column93", type any}, {"Column94", type any}, {"Column95", type any}, {"Column96", type any}, {"Column97", type any}, {"Column98", type any}, {"Column99", type any}, {"Column100", type any}, {"Column101", type any}, {"Column102", type any}, {"Column103", type any}, {"Column104", type any}, {"Column105", type any}, {"Column106", type any}, {"Column107", type any}, {"Column108", type any}, {"Column109", type any}, {"Column110", type any}, {"Column111", type any}, {"Column112", type any}, {"Column113", type text}, {"Column114", type any}, {"Column115", type any}, {"Column116", type any}, {"Column117", type any}, {"Column118", type any}, {"Column119", type any}, {"Column120", type any}, {"Column121", type any}, {"Column122", type any}, {"Column123", type any}, {"Column124", type any}, {"Column125", type any}, {"Column126", type any}, {"Column127", type any}, {"Column128", type any}, {"Column129", type any}, {"Column130", type any}, {"Column131", type any}, {"Column132", type any}, {"Column133", type any}, {"Column134", type any}, {"Column135", type any}, {"Column136", type any}, {"Column137", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type4" = Table.TransformColumnTypes(#"Promoted Headers",{{"454Day_1", type date}, {"454Day_2", type date}, {"454Day_3", type date}, {"454Day_4", type date}, {"454Day_5", type date}, {"454Day_6", type date}, {"454Day_7", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type4",{"KR/JP/ANZ/SGMY #SAP", "Section 1", "454Day_1", "454Day_2", "454Day_3", "454Day_4", "454Day_5", "454Day_6", "454Day_7", "454Quantity_1", "454Quantity_2", "454Quantity_3", "454Quantity_4", "454Quantity_5", "454Quantity_6", "454Quantity_7"}),

// Combine each pair of Day and Quantity columns
CombinedColumns1 = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Other Columns", {{"454Day_1", type text}, {"454Quantity_1", type text}}, "en-US"), {"454Day_1", "454Quantity_1"}, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), "Day1-Quantity1"),
CombinedColumns2 = Table.CombineColumns(Table.TransformColumnTypes(CombinedColumns1, {{"454Day_2", type text}, {"454Quantity_2", type text}}, "en-US"), {"454Day_2", "454Quantity_2"}, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), "Day2-Quantity2"),
CombinedColumns3 = Table.CombineColumns(Table.TransformColumnTypes(CombinedColumns2, {{"454Day_3", type text}, {"454Quantity_3", type text}}, "en-US"), {"454Day_3", "454Quantity_3"}, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), "Day3-Quantity3"),
CombinedColumns4 = Table.CombineColumns(Table.TransformColumnTypes(CombinedColumns3, {{"454Day_4", type text}, {"454Quantity_4", type text}}, "en-US"), {"454Day_4", "454Quantity_4"}, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), "Day4-Quantity4"),
CombinedColumns5 = Table.CombineColumns(Table.TransformColumnTypes(CombinedColumns4, {{"454Day_5", type text}, {"454Quantity_5", type text}}, "en-US"), {"454Day_5", "454Quantity_5"}, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), "Day5-Quantity5"),
CombinedColumns6 = Table.CombineColumns(Table.TransformColumnTypes(CombinedColumns5, {{"454Day_6", type text}, {"454Quantity_6", type text}}, "en-US"), {"454Day_6", "454Quantity_6"}, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), "Day6-Quantity6"),
CombinedColumns7 = Table.CombineColumns(Table.TransformColumnTypes(CombinedColumns6, {{"454Day_7", type text}, {"454Quantity_7", type text}}, "en-US"), {"454Day_7", "454Quantity_7"}, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), "Day7-Quantity7"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(CombinedColumns7, {"KR/JP/ANZ/SGMY #SAP", "Section 1"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1", "Value.2"}),

// Custom date parsing function
ParseDate = (dateText as text) as date =>
let
parts = Text.Split(dateText, "/"),
day = Number.FromText(parts{0}),
month = Number.FromText(parts{1}),
year = Number.FromText(parts{2}),
date = #date(year, month, day)
in
date,

// Convert the date text back to date type with correct format
#"Parsed Dates" = Table.TransformColumns(#"Split Column by Delimiter", {{"Value.1", each ParseDate(_), type date}, {"Value.2", each try Number.FromText(_) otherwise null, Int64.Type}}),

// Replace errors with default date
#"Replaced Errors" = Table.ReplaceErrorValues(#"Parsed Dates", {{"Value.1", #date(1900, 1, 1)}}),

// Rename columns
#"Renamed Columns" = Table.RenameColumns(#"Replaced Errors",{{"Value.1", "Date"}, {"Value.2", "Units"}}),

// Group by Date and sum the units
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Date"}, {{"SUM Units", each List.Sum([Units]), type nullable number}}),

// Replace errors with 0 in the grouped data
#"Replaced Errors1" = Table.ReplaceErrorValues(#"Grouped Rows", {{"SUM Units", 0}}),

// Final type conversion
#"Changed Type3" = Table.TransformColumnTypes(#"Replaced Errors1",{{"Date", type date}})
in
#"Changed Type3"

original date format prior to column merger (01/08/2024 - 1 Aug)date format mixed up during column mergerfinal desired table having the wrong date format (08/01/2024 - 8th Jan)

[v-junyant-msft]

Hi @hafiyco ,

The fact that your date format changes from DD/MM/YYYYY to MM/DD/YYYYY has nothing to do with the M code you are using, it has to do with the culture of the region in which your system is located when you first install Power BI Desktop. For example, I am in the United States, where the default date format is MM/DD/YYYYY, so Power Query will automatically convert the dates in the table to MM/DD/YYYYY after the transformation is completed.

If you need to keep the DD/MM/YYYYY format, you can manually change the data type by region for the date column at the Split Column by Delimiter step, when you just split the merged column into two text-type columns:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hafiyco]

Thanks Dino! It works perfectly now. Thanks for the guidance on this issue! 😄