I am just wondering why
SELECT POWER(2.718, -34.08)
returns me 0.
Tested it in Microsoft Excel
=2.718^-34.08
and it is returning me 1.58774E-15 which is approximate 15 decimal points.
My table column defined as FLOAT which can take up to 54.
Is it the limitation in the POWER function ?
I have the following CASE statement:
SELECT CASE WHEN [TBL_whole_rock_geochem].au = 0 THEN '<' + [TBL_ActLab_codes].[Au] ELSE [TBL_whole_rock_geochem].[Au] END AS [Au det]
It errors on the highlighted code.
Error converting data type nvarchar to float
the code should return :
<0.5
<1
I have tried '<' + ISNUMERIC([TBL_ActLab_codes].[Au]) but received this error
Conversion failed when converting the varchar value '<' to data type int.
How I can concatenate the < with a float value?
I seem to always get the "Fun Stuff" to try and figure out. I have an entire table that was pumped out of Oracle. I even hate saying that word!
There are a couple columns that are Float data type, and they are storing phone numbers as a Float data type. I am not able to CAST these into anything that is legible.
This is one of the values that I made up that look like some of the others.
9.72732e+009
I have a table with three columns: UniqID, Latitude, and Longitude.
I need to write a query to identify when the latitude has more than 6 decimal places past the decimal. Same with Longitude. Values in these attributes can be a negative number. These fields are FLOAT.
I have been given some data from a Mainframe (AS400?) which has some fields coded in Packed Decimal. I have been able to load the data into a SQL2005 database table, but I now need to convert the Packed Decimal data in the binary(6) field to the appropriate integer (or float) value.
The field contains values such as the following:-
0x20202020200C
0x202020022025
0x20202020DFFA
I don't know how to interpret these. Has anyone got a function that can do this for me?
I've read several articles online that explain how packed decimal works, but none tell me how to interpret the last of my three examples. Can you help?
Thanks!
I am trying to show images in a product listing in power view.I work with an excel 2013 desktop version based on an office 365 pro account.I did the following steps:
import of an excel file with an article list via power query and loading the data to the data model import jpg images from a folder via power query, setting content as binary type and loading the data to the data modellinking both tables in power pivot--> manage via the image namesetting the table behavior for the images table under power pivot --> manage --> Advanced (e.g. Default Image: Content)opening power view and building article cards with article number and imageProblem: only a camera icon shows up in power view
Is there a solution with a desktop version?Can I use my Office 365 Pro account to make it work? How?Why is there no solution showing images in a pivot table?Link to Dropbox with power pivot files
Hello,
I'm trying to follow a specification here:
W1 = (W0^0.333 + G * D/1000)^3 (the ^ symbol means 'to the power')
In my stored procedure I have used the following test data with unexpected results. Can anyone tell me if I'm using POWER properly?
@Value = POWER(((POWER(150,(1/3)) + 2) * 10/1000),3 )
Thanks
playing with the Power pivot , DAX. While analyzing the DAX ,I came across a function EVALUATE , but when I tried this function in excel Power Pivot workbook - =EVALUATE 'Date' where 'Date' is my one of the Power pivot table , I was writing this function within the Calculation area of the Power Pivot model. I get the below error when I hit enter after writing the function ."The expression is not valid or appears to be incomplete..An MDX expression was expected while a full statement was specified."But in many forums I find the syntax is correct.
View 4 Replies View RelatedI have a DATESMTD function which is not working. This is what is happening, if there is no row data for the month it creates a month to date total similar to the year to date total instead of zero. See below my formula:
MTDSUM:=CALCULATE(SUM('Combined Years Dataset'[Net]),DATESMTD('Combined Years Dataset'[Period2]),'Date'[date])
Period 2 is a column with dates (end of monthdates) in a table called Combined Years Dataset.
So, if I have $200.00 data for Aug and no data for Sept, the system puts in 200.00 as the month to date for Sept instead of zero. What can I do to make the system insert zero in the month to date column instead of the $200.00. What am I doing wrong in the formula.
In an Excel workbook I'm building a report using a PowerPivot data model.
I've a Calendar filter. If I select an year from this filter I need to show in a cell the total number of working days, present in the Calendar table as a column.
I created a Switch statement below that surprisingly doesn't throw any errors and some of it actually works. The problem is that the function calls in the 3rd and 4th sections of it below (in bold) are not working.
=switch(HASONEVALUE('s1JudgeIds'[JudgeName]),
values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(Time[ExitMonthCategory]),Time[ExitMonthCategory] <= MAX(Time[ExitMonthCategory]))),
values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",calculate([Numerator],all('s1JudgeIds'[JudgeName])),
values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare],all('s1JudgeIds'[JudgeName])),
values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare])
experimenting with powerpivot and I use an simple example of the AdventureworksDW in Powerpivot.
If i use the RELATEd function with Product and ProductSubCategory the column is empty and I expect values.
Relationships are rightfully defined. What am i doing wrong?
I've encountered an issue where the ALLSELECTED function works fine unless I use a date hierarchy, i.e. year, quarter, month, date, for the rows in a pivot and then use a year slicer, select one or more years individually, and the clear the filter on the slicer. The year(s) that I selected in the slicer remain at 100% in the pivot instead of returning to the subtotals for the unfiltered slicer.
This only occurs when I use a calendar hierarchy for rows and a date field for the slicer, either from the hierarchy or a regular date field.Below are images of the normal behavior and then the result after following the steps above. Can't figure out why the calendar hierarchy is causing the issue since it works for all other time functions, etc., and follows best practices such as contiguous dates, etc.
I have created data model where I'm taking several sources of Point of Sale data (multiple retailers) and combing them using Power Pivot and a custom calendar. We get data retailer direct, which is mostly in weeks, and data from IRI which is in four week buckets. This does not allow me to use the date intelligence DAX functions. I'm brand new to DAX and my experience starts and ends with Excel. (Diagram view and link to file to come after account verification) .
The DAX code for calculating LY Sales is:
=
CALCULATE (
[Sales $],
FILTER (
ALL ( dCalendar ),
dCalendar[IRIYearNumber]
= VALUES ( dCalendar[IRIYearNumber] ) - 1
[Code] ....
The filters are to prevent items not on the item table from showing on the report, and the customer filter is to prevent all the sales being rolled together as and extra line (with blank customer) on the report.
The error happens when I select two periods that are in different years. When I select the 13 periods on 2014 all is well. But when I add a period from 2015 it throws the error below;
ERROR - CALCULATION ABORTED: Calculation error in measure 'dProducts'[LY Sales $]: A table of multiple values was supplied where a single value was expected.
I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.
All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little
Some test codeDECLARE@SomeNumber FLOAT,
@BinFloat BINARY(8)
SELECT@SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))
SELECT@SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]
And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE@Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT
SELECT@Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)
WHILE @Part <= 8
BEGIN
SELECT@Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END
WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)
SELECT@Bit = @Bit + 1,
@Mask = @Mask / 2
END
END
RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
Thanks again Jeff!
Peter Larsson
Helsingborg, Sweden
I build a UDF scalar function like this:
CREATE FUNCTION VerificaAcessoPerfil
(
@codigo INT
)
RETURNS INT
[Code] ....
Curiously when i call my function the same one return always the same value, ex:
Select VerificaAcessoPerfil(2)
the return value is : 698 ??
but if i run the Select statment like this:
SELECT DISTINCT codigo,
(case codigo WHEN 1 THEN 695
WHEN 11 THEN 697 WHEN 2 THEN 211
WHEN 10 THEN 698 WHEN 13 THEN 696
WHEN 4 THEN 1 END)[codigo]
FROM pf (NOLOCK) INNER JOIN pfu (NOLOCK) ON pfu.pfstamp=pf.pfstamp
WHERE codigo IN (1,11,2,10,13,4)
ORDER BY 1 ASC
The value are:
1695
2211
41
10698
11697
13696
I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.
The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.
The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.
Population N (denominator):
=CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1]))
Records in time interval (numerator):
=COUNTROWS(analyticJudConsist)
Percent:
=[countrows]/[denominatorCare]
How to get this out put.
Details:
declare @deadline Datetime = '2014-03-23 15:30:10.000'
SELECT CONVERT(VARCHAR(30),@deadline, 100) AS DateConvert
----With this I am able to produce that like
----o/p: Mar 23 2014 3:30PM
declare @deadline1 Datetime = '2014-03-03 15:30:10.000'
SELECT CONVERT(VARCHAR(24),@deadline1, 100) AS DateConvert
--o/p: Mar 3 2014 3:30PM
--expected O/p: Mar 03 2014 03:30PM
What is the correct date format to achieve this.
I'm looking for a way to pull off a complex find+replace within some code, as follows:
@step_name = N'SAME - OCF Collins (Tabard)', @command = N'DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC ', @database_name = N'
We have over 200 variants of the above, but following the same syntax (@step_name, Dbname, @Command etc...
Rules:
1) Note the unique identifier "~Z0x5F4F7B0688825E7544AC46CFD664F98AC". I would like it replaced for whatever is between "@step_name = N'" , and @command = N'DTSRun ; (this will form a filename).
2) Note the 'DTSRun /' string. I'd like that replaced with Dtexec /F "D:MyFileLocationFolderHere" (this folder remains constant).
How can I get the data retrieved from the exec function below into Excel
DECLARE @columns NVARCHAR(MAX) ,
@columns_n NVARCHAR(MAX) ,
@sql NVARCHAR(MAX);
SET @columns = N'';
SET @columns_n = N'';
SELECT @columns += N', X.' + QUOTENAME(aaTrxDim)
[code]....
I am having values as below:
99.87
99.96
8.67
And my output should be as:
99.8
99.9
8.6
How can I do this
I would like to create a table valued function using the following data:
create table #WeightedAVG
(
Segment varchar(20),
orders decimal,
calls int
);
insert into #WeightedAVG
[code].....
I would like to create a function from this where I can input columns, and two numbers to get an average to output in a table ie,
CREATE FUNCTION WeightedAVG(@divisor int, @dividend int, @table varchar, @columns varchar)
returns @Result table
(
col1 varchar(25),
WeightedAVG float
[Code] .....
I am debugging a DB maintance script which creates a table of index maintainance commands which are created separately for each index according to the level of fragmentation and other factors.
For the debugging process, I'm looking for a way to parse each command in the table without actually running them to locate any syntax errors. In other words, as if you clicked the blue check on each one.
Does such a function exist in SQL 2008 (the version I'm doing this on) or other versions?
I have troubles with this scalar-valued UDF:
I get the error:
Select statements included within a function cannot return data to a client.
Is this a proper way to include a CTE in a function?
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[_Pink_FN_StartingDatePLGeographical](@StartingDate AS DATETIME) RETURNS NVARCHAR(20)
[code].....
I've this result from my 'case' query;
Jan Feb Mar April
1 2 3 4
I want ;
Month Value
JAN 1
Feb 2
Mar 3
April 4
I have several reports in a Power View Gallery. In Gallery view, most of the reports show the "Open New Excel Workbook", the "Create Power View Report", and the "Manage Data Refresh" buttons on the right side of the report list. Why would some reports not have these buttons available? In the attached image you can see one report with the buttons and one without the buttons.
View 5 Replies View RelatedI've imported a number of excel sheets into a Power Query Table. All seems to appear ok until I load the data. Of the 15k rows around 2k have a similar error where it cannot convert an integer to type string as below example
Expression.Error: We cannot convert the value 40 to type Text.
Details:
Value=40
Type=Type
The columns in question are all of integer type, I've looked through the M query and there is no conversion to string taking placeThe values where we don't get the error are also integers hence the intriguing question is why does the error occur on a subset and not the others. I suspect there is a limit to the number of errors also somewhere internally M query is converting the column to text for some reason.
Slow loading issue with an if statement. In the raw data the field [Location] is a text field e.g. 0010. I have a parameterised query that get a Location_Value from Excel and passes it to the PQ query using:
#"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
This works fine if you chose a single location. However I wanted to be able to select all locations and text is horrible to work with so in PQ I used the change type function to change the location column into whole numbers. I changed excel to also pass a number as Location_Value. I was therefore surprised when the same query took 2.5 times longer to refresh????
My PQ now looks like this
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Location", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
I'm wondering if I need to do something to the ([Location] = Location_Value) bit as maybe it still thinks [Loation] is text and it is trying to compare it to a number. I st assumed the step above meant that [Location] was now a number, but maybe you still have wrap it with some kind of VALUES or TEXT function?
I have data like below
Country State Rank
India Kerala 1
India Kerala 2
India Kerala 3
India Tamil Nadu 1
India Tamil Nadu 2
India Orissa 1
India Orissa 2
US Florida 1
US Florida 2
US NewYork 1
I have to generate rank like this in power pivot. How can I achieve it?
When updating a Power Query Source in Power Pivot, I'm getting the following error message:
Basically saying that OLE DB or ODBC-error occured when:
- Connections have been imported from a different workbook or
- the workbook has been created in a newer Version of Excel
None of which is the case here. What can cause this?
System: Virtual machine (VMware) on Windows 2008 R2, Office 2010, 64bit, using temporary profile.
I have opened an account in [URL] and taken the 60 days trial for power bi pro. I've developed power pivot and generated power views in share point 2013. But, I'm new to Power BI desktop. I have created a report in power BI desktop and published to [URL]. Also, I have uploaded an excel file directly to [URL] and created the report from the workspace available there itself and pinned the report to dashboard also. Everything is fine till this. But, I need to refresh the file which I have uploaded. I have some dummy data in excel sheet.
ZipCode State ZipName
2345 AA AA
456 BB BB
6787 CC CC
This has been created as a table and then added to data model. So, power pivot has been created for the same. Then I have uploaded this file to [URL] site. But, I'm getting an error message while trying to schedule refresh for the same.
"You cannot schedule refresh for this dataset because it does not contain data model connections. You cannot schedule refresh on worksheet connections or linked tables. To schedule refresh the data must be loaded into the data model."
How can I create a data model connection? How can I schedule refresh for an excel file like this?
I'm a relative newcomer to Power View. I've been playing with charts and have been struggling to combine both line and bar on the same chart. It would appear this functionality is not available. Considering this is basic functionality when it comes to charting, how to achieve this....
View 3 Replies View RelatedI'm looking to replace text in a given column given a set of conditions in the other columns. Please see below the M query in the advance editor and in particular the bold text. Here I've created a new entry that would appear in the query applied steps window in the power query editor that I have called "Replace Values". The logic is if Data.Column4 column equals "London" then replace null values in Data.Column5 with London. However when I save the query below I get the error
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
I plan to change the expression to test for multiple conditions however I need to get the basic expression working first. The other frustration i had with the "if" statement is it had to have an else even though I didn't require it, am i doing something wrong here?
let
FullFilePath = "C:PermanentDwellings.xlsx",
Source = Excel.Workbook(File.Contents(FullFilePath)),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16",
[Code] ....