error opening EXCEL spreadsheet, vertipaq error
Hello over the last couple of weeks started getting this error below when opening different spreadsheets. the spreadsheets have worked fine for years.
If anyone out there can help. It would be much appreciated
See also questions close to this topic
Second Highest Salary
Write a SQL query to get the second highest salary from the Employee table.
| Id | Salary | | 1 | 100 | | 2 | 200 | | 3 | 300 |
For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.
| SecondHighestSalary | | 200 |
This is a question from Leetcode, for which I entered the following code:
SELECT CASE WHEN Salary = '' THEN NULL ELSE Salary END AS SecondHighestSalary FROM (SELECT TOP 2 Salary ,ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Num FROM Employee ORDER BY Salary DESC) AS T WHERE T.Num = 2
It says that the query does not return NULL if there's no value for second highest salary.
how to compare meta data of two tables?
i am trying to compare meta data of two tables but i don't how i get table meta data and compare with other table meta data this my tables
eid | ename | country
101 asdf india 102 bsdf india 103 csdf india
104 dsdf india
eid | ename | country
101 asdf india 102 bsdf usa 103 csdf usa
104 dsdf india
can someone help me plz?
improve query performance in 300 million row table
i need to improve the performance of a query which takes way too long. The query is used in an SSIS data flow in the source task. It is part of a longer query but this is what causes the problem so i narrowed it down as follows:
select xa.*, s.idA from tableA s inner join tableB xa on s.idA = xa.idB where xa.type = 'type_A'
TableA = 80 million rows TableB = 340 million rows
TableA has a non clustered index on idA and TableB a non clustered index on idB.The execution plan uses the index of TableA and performs a full table scan on TableB which takes 98% of the effort. I have tried to create a non clustered index on TableB, on field idB including column 'Type'. I had to kill the create query after an hour because it was suspended with a CXPACKET wait type. Probably it was waiting to read all the data but my SSIS flows can't afford to waste resources for so much time. I then tried to create a clustered index on tableB column type, which i thought would take less time. The plan was to modify the query using a cte like this:
;with tmp as ( select * from tableB where type = 'Type_A' ) select xa.*, s.idA from tableA s inner join tmp xa on s.idA = xa.idB
Using this query i will have only 200k rows in the tmp table and i will avoid full table scan to filter tableB utilizing all the available indexes so i think it will reasonably improve the speed
However, i had to kill the create query as it was taking more than 1 hour (suspended again, CXPACKET again). So my question is: Is it possible to speed up the index creation process? Is it possible to estimate the time needed for the index creation? Does anyone has any other recommendation to speed up the query?
EXCEL-VBA: Save userform controls as picture
I'm trying to develop a macro that updates/generates the footer/header of excel files for my company. The first version of this macro uses a sheet to store the required info (pics, text) and it works. I want to go one step further and eliminate the sheet where the macro info is stored by moving all required info in userforms.
So, I have UserForm1 which has the company logo picture. This is saved using
SavePicture UserForm1.Logo.Picture, "D:\Logo.png"where Logo is an Image control.
In UserForm2, I have a Frame control (named FooterText), populated with labels which need to be updated prior to saving the entire frame as png (or whatever picture format). Needless to say that
SavePicture UserForm2.FooterText.Picture, "D:\Footer.png"does not work. The text for the footer exceeds the limit set by office and as such, can only be added as a picture.
Are there any options for me at this point? Is there a simple solution for exporting userform controls as picture? I don't want to complicate things by adding new references since it will be required by all company users.
Convert .csv file to .xls using Google AppScript
I have a use case wherein I have to fetch BigQuery result and store it in an excel file. Since BigQuery by default does not have any such provision, I was thinking of exporting the query result in CSV format and then convert it to excel format using Google AppScript. We tried doing this using Sheet-js but it was not working in AppScript.
We also could not use .csv file directly because it converted integer values like '00014' to '14' and we cannot have that. We also tried using Google Spreadsheet but it seems there is a limitation of 10 million cells per workbook. Please correct me if I'm wrong.
Can someone please suggest what approach I must take to achieve this. Any kind of help will be appreciated.
Diff 2 file CSV Comparing
I have 2 file CSV with every line "ID" "First Name" "Last Name" and other data.
I need to compare the 2 files and save the difference between csv1 and csv2 in a other file csv.
The id can change in the 2 files, so the same people can be in csv1 .
The line number
4 (ID 4)and in csv2 the line number
6000 (ID 6000).
The 2 file are very big, all 2 the files have between
6000 and 8000 id.
How can i do?
How to create a measure in Power Pivot that will calculate unit counts by category for a specific point of time in the future?
I need to be able to report on available units summarized by equipment category for a specific point of time in the future (for instance, Nov. 11th, 2018) based on given start and end usage dates.
I think it can be achieved via a measure in Power Pivot and date table but I am just quite new to DAX and time dimension concept overall.
My measure reads: AvailUnitCount:=CALCULATE(DISTINCTCOUNT(EquipUsage[EquipmentNo]))
How do I incorporate time dimension into the measure above, so I can report on available equipment for a specific date by moving a timeline in Excel?
Please see the data set and desired outcome below. I will immensely appreciate your advice on this. Thanks.
Table 1: EquipUsage
EquipNo CategoryNo UsageStartDate UsageEndDate
10005164 A020004004 5-Nov-18 5-Dec-18
10005167 A020004004 24-Oct-18 10-Nov-18
10005176 A020004005 9-Oct-18 5-Dec-18
10015982 A020004006 18-Feb-18 5-Sep-18
10019170 A020004006 16-Aug-18 30-Mar-19
10019551 A020004006 2-May-17 10-Nov-18
10005178 A020004007 20-Sep-18 15-Jan-19
Table 2: EquipCategories (Example of Desired Outcome for November 11, 2018)
CategoryNo AllUnits AvailableUnits
A020004004 2 1
A020004005 1 0
A020004006 3 2
A020004007 1 1
Data connection to SQL SMS when loading table to Power Query - Data compression
I would like to load a database from SQL Server Management Studio to a Power Pivot but I see in my SQL SMS that the size of the table is :
And when I load it it changes to 135,6 MB. I think it is because I see it creates a connection between SQL SMS and the Power Pivot:
If that is the case, is there any way of deleting the connection? Or maybe it is another reason for the size to get bigger?
Thanks for your help!
DAX formula that allows me to count values in multiple columns for an array (1 to 12 for months)
Here's a sample of my table (
PlannedMonthis the planned month during which job
IDis supposed to be done,
RealMonthis the month in which job
IDis really done :
ID PlannedMonth RealMonth -------------------------------- 1 4 6 2 3 9 3 3 0
What I want to generate as a pivot table is the following information, a cumulative count of values across multiple columns:
Month Planned Real -------------------------- 3 2 0 4 3 0 6 3 1 9 3 2
Why a pivot table instead of a simple usage of
countifsin Excel? Because I have other columns that present a region and a country and I want to add filters to my table so people can fetch the information that is pertinent to them.
Since I want to add filters for data presented in my table, I think a Helper Table wouldn't be of much use (correct me if I'm wrong though) and since I already have a load of table, I'd rather go for a measure solution contained in the PowerPivot table itself.
Solution attempted :
Use of a helper table that enumerated months from 1 to 12 in a column, then
CALCULATE(COUNTA(the months in
RealMonth. This solution works if I don't want to filter on columns in the original table (i.e. filter by region, country, etc.).