Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Can somebody help me with the Power Query code by which I can update the value for Column Spend for against ProductTitle Diff whose formula is Base = 1 – SUM(Spend). Data may increase and pattern is shown below. The source is SharePoint list. I will have to show this data on Donut chart in Power BI.
Spend ReportDate ProductTitle Spend ReportDate ProductTitle
0.3 5/4/2016 A 0.3 5/4/2016 A
0.53 5/4/2016 B 0.53 5/4/2016 B
5/4/2016 Base 0.17 5/4/2016 Base
Spend ReportDate ProductTitle Spend ReportDate ProductTitle
0.3 5/4/2016 A 0.3 5/4/2016 A
0.53 5/4/2016 B 0.53 5/4/2016 B
5/4/2016 Base 0.17 5/4/2016 Base
0.5 6/5/2016 A 0.5 6/5/2016 A
0.38 6/5/2016 B 0.38 6/5/2016 B
6/5/2016 Base 0.12 6/5/2016 Base
Solved! Go to Solution.
Here are the detailed steps for achieving this. I am asuming that your Product title has same pattren.
I have used below sample data in excel
Connected with Excel and edit the query
After this remove the two steps from right area "Chagne Type" and Promoted Headers". It will bring your column hearders as data row. Now remove first row by going to Home Table and clicking Remove Rows button. You need to mention 1 as number of rows to be deleted.
Now your data set will look like this.
Now select Column3 and click on Pivot Column button under Transformations Tab
select Column1 as Value column in popup and click Ok. It will pivot your data to a new format as below
Now remove the Base Column and Add new Column Base with Below Formula
You base value has been calculated. See below for reference
Here are the detailed steps for achieving this. I am asuming that your Product title has same pattren.
I have used below sample data in excel
Connected with Excel and edit the query
After this remove the two steps from right area "Chagne Type" and Promoted Headers". It will bring your column hearders as data row. Now remove first row by going to Home Table and clicking Remove Rows button. You need to mention 1 as number of rows to be deleted.
Now your data set will look like this.
Now select Column3 and click on Pivot Column button under Transformations Tab
select Column1 as Value column in popup and click Ok. It will pivot your data to a new format as below
Now remove the Base Column and Add new Column Base with Below Formula
You base value has been calculated. See below for reference
Thank You so much Habib for your time and support. This solution has helped me to complete my task. Thanks again...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.