Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I want to make a new column for showing the latest bin code for each serial no - I cannot figure it out how to do it.
I have tried Group By in Power Query, but doesn't work.
I want something like below. The first 4 column are raw data, and the last are the data I need.
Registering Date | Quantity | Serial No_ | Bin Code | LATEST BIN CODE |
26-09-2023 00:00 | 1 | SN000014 | 30601 | RENT-0253 |
26-10-2023 00:00 | -1 | SN000014 | TRANSIT | RENT-0253 |
27-09-2023 00:00 | -1 | SN000014 | 30601 | RENT-0253 |
27-09-2023 00:00 | 1 | SN000014 | RENTAL | RENT-0253 |
26-10-2023 00:00 | 1 | SN000014 | RENT-0253 | RENT-0253 |
13-03-2023 00:00 | -1 | SN166 | TRANSIT | RENT-0188 |
03-02-2023 00:00 | 1 | SN166 | TRANSIT-REP | RENT-0188 |
06-02-2023 00:00 | -1 | SN166 | TRANSIT-REP | RENT-0188 |
06-02-2023 00:00 | 1 | SN166 | RENT-0188 | RENT-0188 |
27-02-2023 00:00 | -1 | SN166 | RENT-0188 | RENT-0188 |
13-03-2023 00:00 | 1 | SN00329 | 70311 | RENT-0226 |
04-07-2023 00:00 | 1 | SN00329 | 30601 | RENT-0226 |
13-06-2023 00:00 | -1 | SN00329 | 70311 | RENT-0226 |
23-06-2023 00:00 | -1 | SN00329 | 30601 | RENT-0226 |
23-06-2023 00:00 | 1 | SN00329 | 11024 | RENT-0226 |
13-06-2023 00:00 | 1 | SN00329 | RENT-0226 | RENT-0226 |
04-07-2023 00:00 | -1 | SN00329 | RENT-0226 | RENT-0226 |
13-03-2023 00:00 | 1 | SN00365 | 70311 | 11023 |
07-07-2023 00:00 | -1 | SN00365 | 70311 | 11023 |
07-07-2023 00:00 | 1 | SN00365 | 11023 | 11023 |
28-11-2022 00:00 | -1 | SN753 | TRANSIT | TRANSIT |
15-02-2023 00:00 | -1 | SN753 | TRANSIT | TRANSIT |
22-12-2022 00:00 | 1 | SN753 | 21413 | TRANSIT |
04-01-2023 00:00 | -1 | SN753 | TRANSIT | TRANSIT |
I hope someone can help me 🙂 - Thank you in advance.
Solved! Go to Solution.
Hello @MDO ,
Please find attached file for your reference. But the output is not mentioned correctly as per your example because for serial number SN166 latest date is 13th March so the Latest Bin Code will be TRANSIT not RENT-0188. So these things are taken care in the attached eample.
DAX will be:
Latest Bin Calculated =
Var One = CALCULATE(MAX('Table'[Registering Date]),ALLEXCEPT('Table','Table'[Serial No_]))
Var Result = CALCULATE(MAX('Table'[Bin Code]),FILTER('Table','Table'[Registering Date] = One))
Return
Result
Output looks as below:
If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!
Add an index column to differentiate codes registered on the same day.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
you wrote what you need
like this
09/26/2023 00:00 1 SN000014 30601 RENT-0253
Hello @MDO ,
Please find attached file for your reference. But the output is not mentioned correctly as per your example because for serial number SN166 latest date is 13th March so the Latest Bin Code will be TRANSIT not RENT-0188. So these things are taken care in the attached eample.
DAX will be:
Latest Bin Calculated =
Var One = CALCULATE(MAX('Table'[Registering Date]),ALLEXCEPT('Table','Table'[Serial No_]))
Var Result = CALCULATE(MAX('Table'[Bin Code]),FILTER('Table','Table'[Registering Date] = One))
Return
Result
Output looks as below:
If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!
User | Count |
---|---|
128 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |