Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
saurabhkul
New Member

Divide values within column to create new column

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?

 

image.png

 

Thanks,

Saurabh

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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'

1.PNG

 

2. Pivot the duplicated table.

2.PNG

3.PNG

 

3. Merge the source table with the duplicated one.

4.PNG

 

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

5.PNG

 

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

6.PNG

 

6. Add a custom column.

7.PNG

 

7. Remove 'Value' field.

 

8. Pivot column.

8.PNG

 

9. Final output.

9.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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'

1.PNG

 

2. Pivot the duplicated table.

2.PNG

3.PNG

 

3. Merge the source table with the duplicated one.

4.PNG

 

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

5.PNG

 

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

6.PNG

 

6. Add a custom column.

7.PNG

 

7. Remove 'Value' field.

 

8. Pivot column.

8.PNG

 

9. Final output.

9.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

PBI1.pngPBI2.png

 

Thanks,

Saurabh

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.