Search results

That's it GraH :) I hadn't noticed the excel version up to now and so I have just saved one of the original .xls workbooks as a .xlsx and made no other changes and it worked perfectly with the correct dates. I will update them all now. Thank you very much for your help.

Hi GraH, You are on to something. When I run a PQ within one of the workbooks with the data by importing "From Table" and change the format to date they remain in the correct year. If I run the PQ from a new workbook and import "From Workbook" the dates get corrupted. regards, Mike

Hi GraH, when I get back to work on Monday I will set up some tests as well to see if this is particular to this PQ or not . Thanks so much for your interest.

Hi GraH, it interesting that it works for you without needing to change the format. When I change the format to a date "*14/03/2012" it holds the 2018 year as I would hope. My full code is below. [let CharsToRemove = List.Transform({32,46,33..45,47,58..126}, each Character.FromNumber(_))...

Thanks AliGW and GraH. Preparing to reply I think I have found the problem. The dates in the original excel workbook are not formulas but are entered into each cell as 31/12/2018 and appear as 31/12/2018 in the formula bar but appear in the cell as 31-Dec. . So checking what the custom format...

Hi, I have a power query that gets data from a folder with a number of excel workbooks. It worked well last year but when I refreshed it today all the 2018 dates changed to 2019 in the power query. The dates in the excel work books are in custom format eg 12/01/2018 and one of the power query...

Hi Narayan, thanks for the question. Subtotal by code please. So in the example I would like 4 separate subtotals for July. One summing all the 11 coded hours in July and the same for 15,21 and 22.

Hi, I am trying to sum the number of hours each staff member does each month by funder code where the date and funder code needs to be checked first. The timesheets are fortnightly so their periods (as in the attached example) may contain data from either the previous month or the following...

Sorry about that - not pleasant at all.

Sounds like you didn't need anything so pretentious as a 'management style', just stand in front of them and suddenly there's no problem. Am envious about your retirement. cheers, Clarky

Bobhc, man management skills are best left to others I think, it's minefield. :) Thanks for your comments but I will see if someone can come up with some code. If not, then we'll have to carry on as we have done up to now. cheers, clarky

Sorry bobhc, I'm clicking the wrong buttons:( . I certainly agree with your suggestion about sorting out the source document but it is not something I have access to and our IT department is small and it is not on their radar at the moment as they have some big projects on so tweaking this is...

HI bobhc, yes we've found some of those problems you mentioned and that's why we want to remove them. We haven't set up this formatting, it is how the download comes from our intranet and there is little to no hope of getting that changed. So are you suggesting that if I highlight the rows...

Hi, I would appreciate any help with the following please. We download a spreadsheet from our intranet in the format shown in the attachment. The problem is column M "outcomes" because there can be more than one outcome per ID in column A. This leads to that ID entry having merged cells across...

Thanks Hui, i'll go ahead and look at those options. I've been passively following this site for quite some time and have used many ideas for from Chandoo's charts to liven up my charts among other things. Keep the good work up.

Hi, I have a graph that has 189 series of data. I have a mechanism for selecting which ones to display (use of checkboxes not a pivot table) so that they are not all visible at the same time! However each week I need to add another set of values for each series. Is there an easy way of doing this?