PBI DEsktop not refreshing correctly

[Pmorg73]

Hi everyone

I have an issue I have been observing for a couple of weeks and it has not resolved itself.

When I refresh manually my Desktop report it is not updating to the lastest data. The published one on the server is updating and appears correct.

The data are a series of CSV files stored in Sharepoint.

I deleted my credentials and re-entered them, but it is still doing it. Any suggestions? Its very odd.

example below:

Desktop table

Service version

[Pmorg73 · Accepted solution]

So after a little thinking and digging I figured it out.

At some time I had directly imported a Excel file for a sanity check on something. I removed it from the model, but the sample file locations still remained in power query. So even so they were not used any more they referred to a folder that no longer existed. so they caused the Refresh to fail. Individually refreshing my tables still worked but that was labourious. Went through 30 or so power queries to find 3 or so that were broken. Deleted them and then a full refresh worked. It miight be worth a suggestion to have a hold on the fresh screen so when there is an error message it is there long enough for oyu to read it ha. All goo dnow

[KNP]

You mentioned there are 14 csv files but the first filter step is for one file.

Is all the detail in this one file?

[Pmorg73]

So.  Ijust ran the refresh, scrolled tot he bottom and watched. It flashed a warning message and closed. It was so quick i could not see what it was. So I am thinking now that one of my tables is not working which is killing the refresh. I feel that it is credentials so I will play with that area again. Is there a way to see error reports from the refresh?

[KNP]

Not sure about a warning flashing up but if none of the queries have errors (e.g. all refreshing without error) then I don't think that is the issue.

If you have that many individual queries, how are you combining them at the end?

[Pmorg73]

I build a relationship model based around "job number" each csv containing that info. Our time management web site is Workflow Max and it reports out time, invoicing, costs etc as different reports. so my owkr around was to export each report that I might need and then make the relationships 1 to many from a job list. The code I pasted in was for the job list.

I am currently right clioking and individually refreshing each table. And a third of the way through they have all loaded the rows without errors. A refresh all is flashing something but its a fraction of a second so I can not see what triggers it


Thanks for your help though. As I said it works in the service so worst case I publish then refresh that one to get my reports to display.

[KNP]

Right, I thought the files were all the same format and then combined after, I understand, they all have different data.

Longshot, and really unlikely, but make sure you have the 'Include in report refresh' ticked on the queries.

If you click on the query in the query editor that the data is missing from in Desktop, I assume it doesn't show there either?

[Pmorg73]

I do have a few static data queries for lets say 2019 data. So I do have a few that do not fully refresh each run. So I untick Enable Load, but leave the Include in refresh button ticked

After going through all the sheets and individually refreshing. They all loaded and the report updated. Its really weird. Not the end of the world, just a frustration

[KNP]

That is odd. Those are the worst issues to try and debug. 

Glad it's (kinda) working now. 

[Pmorg73]

there are 14 versions of this type of code for each one

[KNP]

Ok, that's not really the intended way of doing this. You could have one query to process all files, but I don't think I can solve that issue for you here. It would take a couple of hours consulting to do that. 

[KNP]

Not enough info to answer this.

Can you share your Power Query code (obfuscate any info that is sensitive)?

[Pmorg73]

What the images show are simple tables from the data.It has worked for 3 years but has recently stopped using. There are 14 csv imports in to this model so picking out powerquery is hard in a small window. But I used stadard import from sharepoint importing.

[KNP]

Sorry, I meant go to the advanced editor and copy/paste the code.

Make sure you paste it into a code block...

[Pmorg73]

yeah I thought you did. But not I have not "written" anything in advanced editor, just standard clicks in the transform window

let
Source = SharePoint.Files("https://*********.sharepoint.com/sites/PowerBIDataset/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "PJM - BI - 02_Job List_expanded.csv")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Hidden Files1", each [Attributes]?[Hidden]? <> true),
#"Filtered Hidden Files3" = Table.SelectRows(#"Filtered Hidden Files2", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files3", "Transform File (16)", each #"Transform File (16)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (16)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (16)", Table.ColumnNames(#"Transform File (16)"(#"Sample File (16)"))),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded Table Column1",{"Column17"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Columns2",{"Source.Name"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"[Job] Job No.", "Job No."}, {"[Job] Job Summary", "Job Summary"}, {"[Category] Category", "Category"}, {"[Job] Office Location", "Office"}, {"[State] State", "State"}, {"[Client] Client", "Client"}, {"[Job] Budget", "Budget"}, {"[Job] Status Code", "Status Code"}, {"[Job] Manager", "Responsible Engineer"}, {"[Job] Date of new SFA/new work", "Date of new SFA/new work"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Budget", Currency.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Job No.", "Job No. - Copy"),
Custom1 = Table.SplitColumn(#"Duplicated Column", "Job No. - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Job No. - Copy.1", "Job No. - Copy.2", "Job No. - Copy.3"}),
#"Removed Columns1" = Table.RemoveColumns(Custom1,{"Job No. - Copy.2", "Job No. - Copy.3"}),
Custom2 = Table.SplitColumn(#"Removed Columns1", "Job No. - Copy.1", Splitter.SplitTextByPositions({0, 2}, true), {"Job No. - Copy.1.1", "Job No. - Copy.1.2"}),
#"Renamed Columns2" = Table.RenameColumns(Custom2,{{"Job No. - Copy.1.2", "Job No. - Month"}, {"Job No. - Copy.1.1", "Job No. - Year"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Job No. - Month", Int64.Type}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type1", each not Text.Contains([#"Job No."], "[") and not Text.Contains([#"Job No."], "Test") and not Text.Contains([#"Job No."], "TEST")),
#"Renamed Columns3" = Table.RenameColumns(#"Filtered Rows2",{{"[Job] Design Engineer", "Design Engineer"}}),
#"Renamed Columns4" = Table.RenameColumns(#"Renamed Columns3",{{"[Job] Start Date", "Job Start Date"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns4",{{"Job Start Date", type date}}),
#"Renamed Columns5" = Table.RenameColumns(#"Changed Type2",{{"[Job] Job Region", "Job Region"}, {"[Job] Client Agent / Architect", "Client Agent / Architect"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns5",{{"Job No.", Order.Ascending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Job No."}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Duplicates",{{"Date of new SFA/new work", type date}}),
#"Renamed Columns6" = Table.RenameColumns(#"Changed Type3",{{"[Job] Eng Tech", "Eng Tech"}, {"[Job] Design Eng / Tech Support", "Design Eng / Tech Support"}})
in
#"Renamed Columns6"

[Pmorg73]

let
    Source = SharePoint.Files("https://batchelarmcdougall.sharepoint.com/sites/PowerBIDataset/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "PJM - BI - 02_Job List_expanded.csv")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Hidden Files1", each [Attributes]?[Hidden]? <> true),
    #"Filtered Hidden Files3" = Table.SelectRows(#"Filtered Hidden Files2", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files3", "Transform File (16)", each #"Transform File (16)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (16)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (16)", Table.ColumnNames(#"Transform File (16)"(#"Sample File (16)"))),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Table Column1",{"Column17"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Columns2",{"Source.Name"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"[Job] Job No.", "Job No."}, {"[Job] Job Summary", "Job Summary"}, {"[Category] Category", "Category"}, {"[Job] Office Location", "Office"}, {"[State] State", "State"}, {"[Client] Client", "Client"}, {"[Job] Budget", "Budget"}, {"[Job] Status Code", "Status Code"}, {"[Job] Manager", "Responsible Engineer"}, {"[Job] Date of new SFA/new work", "Date of new SFA/new work"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Budget", Currency.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Job No.", "Job No. - Copy"),
    Custom1 = Table.SplitColumn(#"Duplicated Column", "Job No. - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Job No. - Copy.1", "Job No. - Copy.2", "Job No. - Copy.3"}),
    #"Removed Columns1" = Table.RemoveColumns(Custom1,{"Job No. - Copy.2", "Job No. - Copy.3"}),
    Custom2 = Table.SplitColumn(#"Removed Columns1", "Job No. - Copy.1", Splitter.SplitTextByPositions({0, 2}, true), {"Job No. - Copy.1.1", "Job No. - Copy.1.2"}),
    #"Renamed Columns2" = Table.RenameColumns(Custom2,{{"Job No. - Copy.1.2", "Job No. - Month"}, {"Job No. - Copy.1.1", "Job No. - Year"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Job No. - Month", Int64.Type}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type1", each not Text.Contains([#"Job No."], "[") and not Text.Contains([#"Job No."], "Test") and not Text.Contains([#"Job No."], "TEST")),
    #"Renamed Columns3" = Table.RenameColumns(#"Filtered Rows2",{{"[Job] Design Engineer", "Design Engineer"}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Renamed Columns3",{{"[Job] Start Date", "Job Start Date"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns4",{{"Job Start Date", type date}}),
    #"Renamed Columns5" = Table.RenameColumns(#"Changed Type2",{{"[Job] Job Region", "Job Region"}, {"[Job] Client Agent / Architect", "Client Agent / Architect"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns5",{{"Job No.", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Job No."}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Duplicates",{{"Date of new SFA/new work", type date}}),
    #"Renamed Columns6" = Table.RenameColumns(#"Changed Type3",{{"[Job] Eng Tech", "Eng Tech"}, {"[Job] Design Eng / Tech Support", "Design Eng / Tech Support"}})
in
    #"Renamed Columns6"

[Pmorg73]

It appears to do the refresh, but the table does not update. In the service the published one does update so the code should be correct I think.