excel - 在 Power Query 中使用多个维度按日期填充缺失的累积值

Question

在 Power Query 中，给定一个基于Cumulative Unique Count日期（Start of Week表中的其他值列（Count和Unique Count in Week）应填写为 null 或 0。

示例表：

标准一	标准二	一周开始	数数	每周唯一计数	累积唯一计数
一个	C	2020 年 3 月 22 日	4	1	1
一个	C	2020 年 3 月 29 日	9	3	4
一个	C	2020 年 4 月 5 日	3	2	5
一个	C	2020 年 4 月 12 日	10	8	9
一个	D	2020 年 3 月 22 日	3	3	3
一个	D	2020 年 4 月 5 日	8	5	6
一个	D	2020 年 4 月 12 日	2	3	9
乙	C	2020 年 3 月 29 日	6	4	4
乙	C	2020 年 4 月 12 日	12	9	10
乙	D	2020 年 3 月 29 日	8	4	4
乙	D	2020 年 4 月 5 日	6	6	7
乙	D	2020 年 4 月 12 日	9	7	9

结果应该是这样的（没有Comment列）：

标准一	标准二	一周开始	数数	每周唯一计数	累积唯一计数	评论
一个	C	2020 年 3 月 22 日	4	1	1
一个	C	2020 年 3 月 29 日	9	3	4
一个	C	2020 年 4 月 5 日	3	2	5
一个	C	2020 年 4 月 12 日	10	8	9
一个	D	2020 年 3 月 22 日	3	3	3
一个	D	2020 年 3 月 29 日	0	0	3	填写
一个	D	2020 年 4 月 5 日	8	5	6
一个	D	2020 年 4 月 12 日	2	3	9
乙	C	2020 年 3 月 22 日	0	0	0	充满
乙	C	2020 年 3 月 29 日	6	4	4
乙	C	2020 年 4 月 5 日	0	0	4	填写
乙	C	2020 年 4 月 12 日	12	9	10
乙	D	2020 年 3 月 22 日	0	0	0	充满
乙	D	2020 年 3 月 29 日	8	4	4
乙	D	2020 年 4 月 5 日	6	6	7
乙	D	2020 年 4 月 12 日	9	7	9

像这里和这里建议的那样进行透视、填写，然后取消透视是行不通的，因为有多个标准，而且标准有时包含空值。

标签： excelpowerquerym

Answer 1

这是一种不同的 Power Query 方法：

• 创建所有可能周的列表
• 按标准分组
• 为每个组创建一个子表
  • 创建缺少周数的列表
  • 对于每个缺失的一周，创建一个记录，其中
    • 标准从组中继承
    • 日期根据缺少的周列表递增
    • 各种计数设置为 Null（如果您愿意，可以将 设置为零，或者通过更改 List.Generate 函数中的值来设置其他任何值）
• 然后展开子表并设置类型

M代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Criteria One", type text}, {"Criteria Two", type text}, {"Start of Week", type date}, 
        {"Count", Int64.Type}, {"Unique Count in Week", Int64.Type}, {"Cumulative Unique Count", Int64.Type}}),

//create a list of all the possible weeks
    wkFirst = List.Min(#"Changed Type"[Start of Week]),
    wkLast = List.Max(#"Changed Type"[Start of Week]),
    numWeeks = Duration.Days(wkLast-wkFirst)/7+1,
    allWeeks = List.Dates(wkFirst,numWeeks,#duration(7,0,0,0)),

//Group; then add missing weeks to each sub-table    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Criteria One", "Criteria Two"}, {
        {"All", each _, type table [Criteria One=nullable text, Criteria Two=nullable text, Start of Week=nullable date, Count=nullable number, Unique Count in Week=nullable number, Cumulative Unique Count=nullable number]},
        {"with All weeks", each if Table.RowCount(_) = List.Count(allWeeks) then _

            //create the subtables whereby we fill in the missing weeks
            //and create a record for the missing weeks whereby the Criteria are carried over
            //and the Counts are "nulled".
            //if you want to make the counts Zero, just change that within the List.Generate function
                else Table.Sort(
                        Table.Combine({_,   
                    let 
                        missingWeeks = List.RemoveMatchingItems(allWeeks, [Start of Week]),
                        startDts = List.Generate(()=>
                            [Counter =0, Criteria One= [Criteria One]{0}, Criteria Two= [Criteria Two]{0}, Start of Week=missingWeeks{0}, Count=null, Unique Count in Week=null, Cumulative Unique Count=null],
                            each [Counter] < List.Count(missingWeeks),
                            each [Counter = [Counter]+1, Criteria One=[Criteria One], Criteria Two=[Criteria Two], Start of Week=missingWeeks{[Counter]+1}, Count=null, Unique Count in Week=null, Cumulative Unique Count=null])
                    in

                        //Remove the "Counter" column from the Combined subtable
                        //Then Sort by "Start of Week"
                        Table.RemoveColumns(Table.FromRecords(startDts),"Counter")}),
                        {"Start of Week"})
                        }
        }),

//Remove Extra Columns, then expand the subtable, and type it
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Criteria One", "Criteria Two", "All"}),
    #"Expanded with All weeks" = Table.ExpandTableColumn(#"Removed Columns", "with All weeks", {"Criteria One", "Criteria Two", "Start of Week", "Count", "Unique Count in Week", "Cumulative Unique Count"}, {"Criteria One", "Criteria Two", "Start of Week", "Count", "Unique Count in Week", "Cumulative Unique Count"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded with All weeks",{{"Criteria One", type text}, {"Criteria Two", type text}, {"Start of Week", type date}, {"Count", Int64.Type}, {"Unique Count in Week", Int64.Type}, {"Cumulative Unique Count", Int64.Type}})
in
    #"Changed Type1"

原始数据

结果