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.
A bit stumped here.
Trying to enhance a dataset with a column that uses some logic.
The data is coming from general ledger entries with the the two (or more) accounts an document posts to. What I need to do is determine which lines of a document contain the entirety of an action. I know when the total of the rows = 0, that's a complete action.
Sample data:
Doc | Line | Amount |
1 | 1 | 10 |
1 | 2 | -10 |
2 | 1 | 20 |
2 | 2 | -20 |
3 | 1 | 10 |
3 | 2 | 10 |
3 | 3 | -20 |
4 | 1 | 30 |
4 | 2 | -30 |
4 | 3 | 30 |
4 | 4 | 10 |
4 | 5 | -40 |
The data is in order, so starting from row 1 we see that adding row 2 sums to 0 thus one entire action. Similarly, 3 & 4, one entire action. 5, 6, and 7 one action. And so one.
Expected results (label/count actions or flag row):
Doc | Line | Amount | Action | Flag start |
1 | 1 | 10 | 1 | X |
1 | 2 | -10 | 1 | |
2 | 1 | 20 | 2 | X |
2 | 2 | -20 | 2 | |
3 | 1 | 10 | 3 | X |
3 | 2 | 10 | 3 | |
3 | 3 | -20 | 3 | |
4 | 1 | 30 | 4 | X |
4 | 2 | -30 | 4 | |
4 | 3 | 30 | 5 | X |
4 | 4 | 10 | 5 | |
4 | 5 | -40 | 5 |
Any ideas?
Thanks!
Hi,
I think there are two ways to accomplish this, assuming your data structure looks like what you've provided. To me, this feels like this flag should be at the grain of the data, so it could be included as a calculated column in your data source. So, what I've done is sorted the data by Doc, and then Line, and then just created an Index from within the Power Query Editor, with the "starting at 1" option:
From there, I created a calculated column with the following logic:
Close!
Unfortunately, I messed up my sample data. The real data doesn't always have the negative values last, hence the need to sum until we get a zero total.
Updated sample data:
Doc | Line | Amount |
1 | 1 | 10 |
1 | 2 | -10 |
2 | 1 | -20 |
2 | 2 | 20 |
3 | 1 | 10 |
3 | 2 | 10 |
3 | 3 | -20 |
4 | 1 | 30 |
4 | 2 | -30 |
4 | 3 | 30 |
4 | 4 | -40 |
4 | 5 | 10 |
Can you include where you expect the flags in this updated scenario?
Whichever result is easiest to produce.
I could use any of these result columns:
Doc | Line | Amount | Action# | Flag(start) | Flag(end) |
1 | 1 | 10 | 1 | X | |
1 | 2 | -10 | 1 | x | |
2 | 1 | -20 | 2 | x | |
2 | 2 | 20 | 2 | x | |
3 | 1 | 10 | 3 | x | |
3 | 2 | 10 | 3 | ||
3 | 3 | -20 | 3 | x | |
4 | 1 | 30 | 4 | x | |
4 | 2 | -30 | 4 | x | |
4 | 3 | 30 | 5 | x | |
4 | 4 | -40 | 5 | ||
4 | 5 | 10 | 5 | x |
Got it. I think this works, but I created this as a measure now instead of a calculated column like the previous approach. I set this up to represent your "Flag(end)." This could be easily modified for the other scenario if that's desired, though. Here's the new calc:
Uh oh.
Added the column to the actual data, ~550k rows, and Power BI just spins "Working on it"....
Anyone have tips for optimization? Maybe replicate this logic with M? But how?
Not sure what your data architecture/data structure looks like, but if I were you, I would try to put a calculation like this on the DB side. It's at the lowest grain of data and could just be incorporated into your driving table.
Awesome!
I modified to a calculated column and switched to flagging the start.
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.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |