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 column of data comprising blocks of 3 entries. Each block of 3 is unique and I need to add a new column which contains n an incremental value for each of the entries within each block as A, B, C (or 1, 2, 3) then start again at the next unique block.
e.g. there could be 3 entries labelled "car" followed by another 3 entries labelled "bus". I want to assign a value of A in the new column to the first entry of car, B to the second entry, C to the third - then repeat the cycle from A for bus.
Any advice on the best way to accompish this would be greatly appreciated.
Solved! Go to Solution.
Hi @Cul33
In Power query, I would
Hi Phil
Thanks for the reply.
Below is a sample (which is extracted from the preliminary stages of transformation steps, hence the data types are not applied).
Each value corresponds to a heading that I want to create, which could be A, B or C. So for the first block of 3 entries, in rows 67 to 69 - the first entry for period Jan 2015 to Dec 2015 I want to be able to assign a value of "A" in an adjacent column, then for the second entry a value of "B" and the third a value of "C". When a change in Period is identifed, i.e. in row 70, I am wanting to return a value of A again in the adjacent and then to loop through to C - which would repeat for several thousand rows
.
I hope this makes sense.
Hi @Cul33
In Power query, I would
Thanks very much Phil - greatly appreciated. That addressed the issue.
If this were being done in Excel it woud lend itself well to an If function where, if the period is the same add 1 and if it changes return a value of 1 again. That would depend on the first entry having a value of 1 of course. A nested If / VLookup / Match Index could then return the required Heading according to the value returned.
I wonder therefore if there is a way to accompish this with DAX rather than creating a table?
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 |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |