Bug using an if statement in R script

[didierjoc]

Hi everyone,

I am experiencing some trouble with an R script in Power BI.

I aim at converting some data of a column depending on its value.

When I run this script, to replaces all "2" by "100", all 2 gets indeed replaced but by "null", not by 100.

Here's the script :

ctr <- 0
for (day in dataset[,1]) {
ctr <- ctr+1
if (dataset[,1][ctr] == 2) {
dataset[,1][ctr] <- 100
}
}

output <- dataset

Here is a screenshot :

Someone may have a clue.

Thanks

Didier

[v-huizhn-msft]

Hi @didierjoc,

I reproduce your scenario and get expected result as follows.

Type the R script same as yours, and click OK.



Then I get the desired result,  replaces all "2" by "100".



Please check your running script if there is null, or you can share the .pbix file for further analysis.

Best Regards,
Angelia

[didierjoc]

Thanks for your answer @v-huizhn-msft,

I tried again and still have this "null" issue.

Here is the .pbix file :

https://ufile.io/36874

Didier

[v-huizhn-msft]

Hi @didierjoc,

I am not load load the .pbix file. You’d better load the file to one drive. And just pot the share link as follows. Thank you for understanding.



Best Regards,
Angelia

[didierjoc]

Hi @v-huizhn-msft

Here's the OneDrive link : https://1drv.ms/u/s!AvRpEa-Mq679gSrhwjp8UW1yUCp_

Didier

[v-huizhn-msft]

Hi @didierjoc,

Thanks for detail information, I have downloaded it and test. For your issue, it is casued by the data type for "day" column is Text. I try to replace the 0 in TB field using 100, it works fine as follows.



Best Regards,
Angelia

[didierjoc]

Hi @v-huizhn-msft,

Thanks for your reply. The datatype of the "Day" column is indeed text, does that mean that I can do no if statements on it ?

Is there any R script that could, as I want, replace "2" by "100" ?

I tried to add quotation marks before and after numbers in my script to convert them in numbers, like this :

ctr <- 0
for (day in dataset[,1]) {
ctr <- ctr+1
if (dataset[,1][ctr] == "2") {
dataset[,1][ctr] <- "100"
}
}

output <- dataset

But, still, all I get are "null" instead of "100".

Do you have any idea ?

Didier

[v-huizhn-msft]

Hi @didierjoc,

Did you have any operation to your data? It's wierd, it works fine when the "day" field column have all 1,2,3 and so on number values, it still works fine when I change them to text data type. While, it return the null when there are "Dim. 15 Mai." format values. I will try to find other solution, I will post the update when I have solution for your issue. Thanks for understanding.

Best Regards,
Angelia

[didierjoc]

Hi @v-huizhn-msft,

I don't think I have any special operation on my data.

I found an alternative solution though :

- Created a integer column ('nb_Day") full of 0

- Used this script ("text_Day" is the original column) :

tmp <- 0
ctr <- 0

for (day in dataset[,"text_Day"]) {

ctr <- ctr+1

if ( (ctr + 9) %% 10 == 0 ) {
tmp <- (ctr + 9)/10
}

dataset[,"nb_Day"][ctr] <- tmp

}

output <- dataset

Instead of filling in the "Day" column that was getting "null"s, I filled in another column and then deleted the "Day" column which was no longer useful.

I would still like to understand why those "null"s appear.

Thanks

Didier