Adding Decimals based on Row Values

[jambarama]

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 includes the number of decimals to be applied to each column. The table looks like this right now:

| User_ID                | User_Name          | Item_ID | Item_ID | Item_ID | ... | <-- header
| Decimal_Info       | Decimal_Info        | 01          | 00         | 05         | ... | <-- first row
| Item_Description | Item_Description | Item_1   | Item_2   | Item_3  | ... | <-- textual description of header IDs
| User_01               | John Doe             | 93          | 523         | 01435  | ... | <--data rows begin
| User_02               | Jane Doe             | 40            | 1123       | 105341 | ... |

...

I'd like to apply the number of decimals to the data in the column.

| User_ID                | User_Name          | Item_ID | Item_ID | Item_ID | ... | <-- header
| Decimal_Info       | Decimal_Info        | 01          | 00         | 05         | ... | <-- first row
| Item_Description | Item_Description | Item_1   | Item_2   | Item_3  | ... | <-- textual description of header
| User_01               | John Doe             | 9.3          | 523       | 0.01435| ... | <--data begins
| User_02               | Jane Doe             | 4            | 1123       | 1.05341| ... |

...

To add to the difficulty, the data file has about 500 columns, and their number and sequence will vary over time. So I don't believe I can insert new columns next to existing, then delete existing columns.

This may be outside what I can reasonably expect power query to do, but if it is possible, I've been unable to figure it out.

[v-jiascu-msft · Accepted solution]

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.

Best Regards,

Dale

[jambarama]

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.