Power BI报表数据丢失求助：刷新后数据异常消失

问题描述 #

原本运行正常的Power BI报表，从4个数据源拉取数据。常规更新Excel格式的原始数据后，突发数据丢失问题：

• 上周报表显示正常，更新原始数据后刷新报表，表格内容出现异常变化。
• 上周数据也产生异常，6月19日之后的所有数据均被忽略；尝试将0改为1，仅识别到新增的一条数据，后续日期数据仍未被纳入。
• 已尝试重建原始数据、拆分独立文件并重新创建数据转换，均无效果。
• 补充：表格视图显示数据正常，但刷新引入新数据后，周五的数据被莫名移除。

相关代码 #

DAX度量：6wk Chat #

6wk Chat = 
VAR RefDate = MAX ( 'Calling Date'[Call Date] )
var PrevDates = 
    DATESINPERIOD (
        'Prev Date'[Call Date],
        RefDate,
        -42,
        DAY
    )
VAR Result =
    CALCULATE (
        SUM(chats[chats]),
        REMOVEFILTERS ( 'Calling Date' ),
        KEEPFILTERS ( PrevDates ),
        USERELATIONSHIP ( 'Calling Date'[Call Date], 'Prev Date'[Call Date] )
    )
RETURN
    Result

Power Query主查询 #

let
    Source = Folder.Files("C:\Users\...\chats"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (3)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"year", Int64.Type}, {"month", type text}, {"day", Int64.Type}, {"chats", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "date", each Date.FromText(Text.PadStart(Text.From([day]),2,"0") & " " & [month] & " " & Text.From([year]))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"date", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Source.Name", "date", "year", "month", "day", "chats"})
in
    #"Reordered Columns"

Transform File (3)查询 #

let
    Source = Excel.Workbook(Parameter3, null, false),
    Sheet1_sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Removed Bottom Rows" = Table.RemoveLastN(Sheet1_sheet, 44),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Bottom Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"year", Int64.Type}, {"month", type text}, {"day", Int64.Type}, {"chats", Int64.Type}})
in
    #"Changed Type"

恳请协助排查该数据丢失及刷新异常问题。

内容的提问来源于stack exchange，提问作者Iakona