Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Im facing a very anoying issue while loading my data. I have a SharePoint list with several colums formated as numbers. See an example:
coulmn_1 | column_2 |
---|---|
2 | |
22 | |
24 | |
2 | 552 |
42 |
So in those columns can be numbers or nothing. When I load my data I want to fill the empty cells with 0, that works fine using following syntax:
LOAD
if([column_1]='',0,[column_1]);
FROM
xxxxx
BUT if I use a straight Table and try to show my Totals using "Expression Total" nothing happens .... For sure I could use "Sum of Rows" but later on I want to format the expression with money() so I need to use "Expression Total" to realize the money format in the Total row. So basicly I didn't even start with anything ... just this most simple example just doesn't work ... what is my mistake?
The weird part is, that if there is a column with only empty cells, so in fact all fields are filled with 0, the "Total Expression" row works ..... so what is that?
Because originally I wanted to import data from the list which already was formatted as a currency .... but same issues there so I tried to look for my mistake in a simple example but yeah ....
Please help me! Im almost becoming desperate.
Thanks for help!
Thanks for the answers. But I don't see the issue in the detection of the empty column .... filling the zeros works. I now used this method:
if(len(trim([column_1]))=0,0,[column_1]),
if(len(trim([column_12))=0,0,[column_2)
still the "Expression Total" doesn't work until that point ...
BUT I found a solution in the manual:
Expression Total
If this option is selected, the expression total will be calculated using all the values of the field. If, e.g., the selected column contains the average salaries for different business categories, choosing the Expression Total option will result in the average salary for all the business categories.
So I just did this in the Straight Table:
sum(column1)
sum(money(column_1))
So thanks anyway for your help!
Could you post a sample qvw please?
Andy
What happens if you try this instead of what you have right now?
LOAD
If(Len(Trim([column_1])) = 0 or IsNull([Column_1]), 0, [column_1]);
FROM
xxxxx
Best,
Sunny
Is that nothing or space or no-break space in your column?
Usually when your column have only Numbers the empty cell should be ignored
For space you can use this:L
LOAD if(column_1=' ',0,column_1) as column_1
FROM
COMAPRE.xlsx
(ooxml, embedded labels, table is Sheet3);
You don't actually need the second check there, the null would have a len of 0 also.
Can just go with below to save the extra code
If(Len(Trim([column_1])) = 0, 0, [column_1])
Joe
Thanks for the answers. But I don't see the issue in the detection of the empty column .... filling the zeros works. I now used this method:
if(len(trim([column_1]))=0,0,[column_1]),
if(len(trim([column_12))=0,0,[column_2)
still the "Expression Total" doesn't work until that point ...
BUT I found a solution in the manual:
Expression Total
If this option is selected, the expression total will be calculated using all the values of the field. If, e.g., the selected column contains the average salaries for different business categories, choosing the Expression Total option will result in the average salary for all the business categories.
So I just did this in the Straight Table:
sum(column1)
sum(money(column_1))
So thanks anyway for your help!