Divide values within column to create new column

[saurabhkul]

Hi,

I'm quite new to Power BI. I need help with a piece of logic I used to implement in QlikView. It is a memory-intensive operation, and we were thinking of executing this piece in Power BI. Our data set looks like the blue cells below.

There are 3 columns: Series_ID, Date and Index Value. Every series ID has an entry for every month for a certain period (here, 2013-2018). It receives an index value for each month (sometimes, it may not). The grey section represents the columns we need to add to the table.

The column Jan|2013 represents the index value rebased to Jan 2013, i.e. for every series ID-month combination, the index value is divided by the index value for that series ID in Jan 2013. This is done for every month until May 2018, i.e. I need to create a new column for every month in the data set.

For example, in the first row, 10/10 = 1. Hence Jan|2013 has 1.

In the second row, 14.5/10 = 1.45. Hence Jan|2013 has 1.45. And so on.

Ideas?

Thanks,

Saurabh

[v-yulgu-msft · Accepted solution]

Hi @saurabhkul,

Suppose the source table is named as 'Test4'. Please refer to below steps that are all applied in Query Editor mode.

1. Duplicate table 'Test4'

2. Pivot the duplicated table.

3. Merge the source table with the duplicated one.

4. Expand columns without selecting 'Series_ID'. Then, unpivot them.

5. Right click the 'Attribute' column, choose "Replace values" option.

6. Add a custom column.

7. Remove 'Value' field.

8. Pivot column.

9. Final output.

Best regards,

Yuliana Gu

[saurabhkul]

Hi Yuliana,

Thanks a lot for the detailed explanantion. However, step 3 (merging) wasn't successful for me. I'll really appreciate your help on this.

Note: The CSV file is 56 MB. Did pivoting such a big table possibly create an issue? Also, I've hidden the Series_ID values due to data sensitivity.

Thanks,

Saurabh