Forum Discussion

jambarama's avatar
jambarama
Regular Visitor
8 years ago
Solved

Adding Decimals based on Row Values

I have a large table. Some of the items in the table should have decimals, but because of the way the data comes to us, decimals aren't included in the table.  Instead, the first row of the table inc...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi jambarama,

     

    Since the number of columns is big and the size of the dataset is large, I would use R to do this job. Please refer to the attachment.

    1. Import the data source;

    2. Remove the third row. (Item_Description);

    3. Change the data type of some columns to the proper type;

    4. Run a piece of R script.

    columns <- length(dataset)
denominator <- 10 ^ dataset[1, 3:columns]
dataset[3: columns] <- mapply("/", dataset[3: columns], denominator)
result <- dataset

    5. Remove the first row.

    Adding_Decimals_based_on_Row_Values

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi jambarama,

 

Since the number of columns is big and the size of the dataset is large, I would use R to do this job. Please refer to the attachment.

1. Import the data source;

2. Remove the third row. (Item_Description);

3. Change the data type of some columns to the proper type;

4. Run a piece of R script.

columns <- length(dataset)
denominator <- 10 ^ dataset[1, 3:columns]
dataset[3: columns] <- mapply("/", dataset[3: columns], denominator)
result <- dataset

5. Remove the first row.

Adding_Decimals_based_on_Row_Values

 

Best Regards,

Dale

jambarama's avatar
jambarama
Regular Visitor
8 years ago

Thanks Dale, I figured it was probably too much to expect from power query.  I'm not sure we want to introduce R into our workflow, but I have some other options to explore. 

 

Thank you for the response.