Search results

Office 365 Using the Excel PQ or Get & Transform query to retrieve data from SQL but returns the above error message on the other users' computers. My PC doesn't get the error and the queries return the data. Searched online but nothing really helpful. I have used the Excel query part for a few...

Thanks p45cal that works great too. I will need to remember to do the sort but it is a simple and fast way to display the information. It will depend on the end user as to how the information is displayed as Pivot or linear format. Thanks to both for a great help. Lee

Thank you it is working very well and what I needed. Thank you very much Narayank991! Lee

The Wage History worksheet has the format for what I am looking for from the Wage Data worksheet. Thanks, Lee

I can download payroll check history for several years into a workbook showing Employee ID Employee Name Check number Check Amount Date I want to create a spreadsheet showing current pay rate, Date of last raise, Raise amount and previous pay rate for each employee. I thought it would be easy...

Thanks, I will try the SQL WHERE and it can be hard coded so the sales people can't change it, it appears to be the easiest for me to do and they can't monkey around with it. However, I like the thought of being able to pass a custom variable using a named range. I will read the links both of...

Is it possible to use a formula to limit the years of information that a Power Query pulls into a workbook from a SQL database? something like: year field is >=YEAR(TODAY())-5 So that the query for year 2017 drops off 2011 pulling in only information from years 2012 to 2017, in the year 2018...

Chihiro, I tried and found that for each criteria range, criteria that is added you must * that criteria on the last criteria statement for COUNTIFS() to sequence. Cool, thanks again.

Narayan, why is it counting the Planted columns with zero as part of the sequence? It should write a zero in those that match and continue with the sequence at 5 instead there are three cells that have 4 in the file that Narayan refers. ??? Chihiro, your formula works!!! Thanks to both of you...

=COUNTIFS(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]],INDEX([Planted],1):[@Planted],"<>0",INDEX([Season],1):[@Season],"=SFW") in the NumCropsPlntd column. It should skip the zero in the Planted column and give sequence count of total crops. ???? Count the number of times the Item...

I used INDEX() in the other criteria ranges and don't get #VALUE error. But it doesn't give the correct answer. I will keep trying and want to say thanks to all for reading and helping. Lee

Let's try this another way. If I put this in one of the cells and copy down it will count in sequence: =COUNTIFS(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]]) but if I try to add criteria then I get #VALUE error except for the last cell that will give me a total =COUNTIFS(INDEX([Item...

Yes I know that COUNTIF() will do that based on ONE criteria and the range being passed as you say. It is the same as I showed in my first post but the formula is inside a TABLE. The data is in TABLE format so the person using it has all the formulas added when they add more ROWS to the bottom...

Using a table format, can COUNTIFS() be made to count each instance in a column based on multiple criteria with the number of that instance instead of giving how many instances? It would give 1, 2, 3 ,4, 5 instead of a total 5 occurrences. Thanks, Lee

Sorry for not making myself understood. Is it possible to make COUNTIFS() with multiple criteria do the same as COUNTIF(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]]) with one criteria? In the CropSPR column has one of the COUNTIF() formula with several IF() statements in front of...

I want to count each occurrence of an item in one column (Item Number) based on more than one variable to give the sequence of the item, 1, 2, 3, 4, 5, 6, etc. using table structure. I use COUNTIF(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]]) and have included a file with the formula...

I want to calculate the change in customer sales comparing 2014 to 2015 sales data using a Pivot Table. I can't get figure out how to do it. Any help is appreciated. The data is pulled in using Power Query from SQL database. Thanks, Lee

I have a list of employees and their work groups. I can use Countifs() to get how many are in each group. Is it possible to get the individuals in the group preferably in a cell with a comma or space between? I can do it with Pivot Table but need it outside a Pivot Table. Thanks, Lee

I heard back for the PQ team. I have the query loading to the Data Model and Worksheet and there is some sort problems when this is done. I load it only to worksheet and it sorts just fine. I don't have any relationships or reasons that I need to load to Data Model in this case so all it well...

I am using version PQ 2.22.4007.242 with 64 bit XL. I sent a frown to the PQ group, they sent an email and I included the screenshots. Thanks again for helping and I will let you know what I hear from the PQ group.

I tried starting a new file as you outlined but no luck. I am using Excel 2013 Standalone to get the BI tools. Is it possible that the PQ install is corrupt? Grasping for answers but thank you for helping me. Lee

I did as you asked and in the preview the data is returned in the sorted order but when it is loaded to the worksheet it is unsorted. All the data appears to be there when I compare the number of rows but it isn't sorted like I want it to be. When I created the files and uploaded them, it was...

I still get as unsorted mess on my end. I hope I have uploaded the files and that you

Yes that is it.

Thanks I figured it out. I have to use the same index() formula for each criteria range inside the countifs() formula so that it pins it to the first cell in the range when using a table and that all the criteria ranges are the same. I should have realized that but forgot and still not used to...