Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I've extracted a simple GL Dataset out of my GL (have it set up so I can extract by the month to keep things simple). Within PowerQuery (Get & Transform) I've shaped my data so that it's pivoted by the date (y axis in pic) and GL Account (x axis in pic). Is it possible for me to now put a command into PowerQuery so that it will simply cumulatively total each of the GL columns top to bottom?
In the example attached, the first 5 columns would remain unchanged from 6/1-6/30 as no balances were added to them. Conversly, in the sixth column (headed by GL 21668), it would read 770000 through June 5th, to then it would read 0 to the bottom.
After this my plan is to then unpivot the columns and I will be left with a cumulative total by date & by GL code.
Any help would be much appreciated, thanks!
Hi,
I would suggest that you unpivot your dataseta and have the GL codes in a single column. So your dataset should be a 3 column one - Date. GL code and Amount. From this dataset build your visual.
Hi Ashish,
Agreed on the unpivot, but prior to doing that, I need to get each GL column cumulatively summed downwards. is this possible without too much sophistication?
Don't know. Sorry.
Not sure about the M but perhaps an easier way is to use create a Measure that calculated Cumulative totals and then use the Summarize in DAX to build a table using DAX (the New Table) button is on the Modeling TAB) that will build a table by Date and GL codes with the Cumulative totals. After the table is created you can link it into your model as you would any other table.
User | Count |
---|---|
87 | |
84 | |
70 | |
62 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |