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.
I have a table in Power query, which besides other fields has the following key fields:
CMT | Year | Week | Customer | Transaction | Value
AB587 | 2019 | 12 | Tom | Purchase | 200
AB587 | 2019 | 12 | Tom | Sale | 15
AB587 | 2019 | 13 | Tom | Purchase | 60
AB587 | 2019 | 13 | Tom | Sale | 100
AB587 |2019 | 12 | Tom | Stock | 1600
AB587 | 2019 | 14 | Tom | Purchase | 50
AB587 | 2019 | 14 | Tom | Sale | 450
This is a table with about 300,000 rows with all the CMT and a couple of year's worth of transactions for all customers.
This is what it looks like right now:
CMT and Transaction as rows, and Weeks as columns, with Value as values, and customer as report filter.
the pivot table obviously shows what's in the raw data. What I want to do is to have the pivot table calculate the Stock for Weeks 13, 14 and so on. In the above example, I would expect the Stock in Week 13 to have 1600-100+60=1560, and Week 14 Stock to have 1560-450+50=1160, and so on.
Basically the pivot table should be projecting the stock in hand. I also want the pivot table to be able to do that when the CMT is removed from the rows and replaced by Customer or any other such combination. One more thing is that if the user brings in "months" instead of Weeks, the Stock should show the value of the last week of each month (the raw data has a month next to week in each row).
Solved! Go to Solution.
If you're worried about performance, you can use this trick to substantially improve speed for a case like this: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power...
Your code would look like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQytTBX0lEyMjC0BFKGRkAiJD8XSAaUFiVnJBangiUNlGJ18CkOTswBKTQ0xarOGJuhZtjNNMYwk7DlJfnJ2SAhMxxKTbDZb0pILdR+EySFFkSEFCHV8KDCrhBHWBFSjAgsgvYjhRZ2tTiCi5BiRHgBVcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CMT = _t, Year = _t, Week = _t, Customer = _t, Transaction = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CMT", type text}, {"Year", Int64.Type}, {"Week", Int64.Type}, {"Customer", type text}, {"Transaction", type text}, {"Value", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Transaction]), "Transaction", "Value"), #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "YearWeek", each [Year] * 100 + [Week], Int64.Type), #"Grouped Rows" = Table.Group(#"Added Custom1", {"CMT"}, {{"All", (Partition) => Table.AddColumn(Partition, "Custom", each List.Sum( Table.AddColumn( Table.SelectRows( Partition, let _earWeek = [YearWeek] in each [YearWeek] <= _earWeek ), "var", each [Sale]- [Purchase] )[var] )) }}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Year", "Week", "Customer", "Purchase", "Sale", "Stock", "YearWeek", "Custom"}, {"Year", "Week", "Customer", "Purchase", "Sale", "Stock", "YearWeek", "Custom"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded All", each [Stock], each [Stock] + [Custom],Replacer.ReplaceValue,{"Stock"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"Stock"}), #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Stock", Int64.Type}}), #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",each [Stock], each [Stock] - [Custom],Replacer.ReplaceValue,{"Stock"}), #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Stock", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"CMT", "Year", "Week", "Customer", "Purchase", "Sale", "Stock"}) in #"Removed Other Columns"
You'll find some more performance tricks here: https://www.thebiccountant.com/speedperformance-aspects/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @nexami ,
if you want to learn how to integrate M code into your own solution, this video might help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-c...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.