powerbi

data appears to disappear in power bi

I have a report in Power BI that has been working. It pulls from 4 data sources. I updated them as usual and am suddenly experiencing data disappearing. This is the report from last week. enter image description here

I updated the raw data, as usual. This is held in an excel spreadsheet.
enter image description here

Refreshing the data then results in the table changing.
enter image description here

Please note that the prior week's data is also appearing changed. It appears that suddenly, everything since June 19th is being ignored. I tried changing the 0 to a 1. The extra was picked up, but it still did not consider any additional days.

I have tried rebuilding the raw data, breaking it out into it's own file, and recreated the transformation, but nothing seems to help.

EDIT: In looking at the table view, I see this.
enter image description here

After I refresh the data to bring in the new data, it looks like this.

enter image description here

It actually removes the Friday off the data for some reason.

Adding the measure in question...

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

The power query code is here...

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"

Here is the Transform File (3) advanced query...

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"
Solution

  • @Leyth resolved this. There was a line that truncated the files when they were being transformed. Things appeared fine until the file grew past a certain limit. Then it removed the lines that extended beyond the threshold. I removed that line (which wasn't needed and I do not recall adding in the first place) and the data appears correctly.