Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I am trying to count just the latest date of a column in a aggregated pool of purchase orders. Every day we take a snapshot of the purchase order's current conditions, at time the buyers change the promised date based on the suppliers best date.
I'd like to record the current days 'Past Due', however there are multiple changes of the date column which compounds the data, makingit pile up and unable to calculate distinct unique shipment dates.
Chat GPT doesnt seem to help me... wondering if the Power BI community has any good workarounds. Keep in mind I am doing a DirectQuery from a coworkers database so Date/Time control will not be in my favor.
Here is an example, i.e. PO# 1144900, Line 2 and Shipment 1 is repeated 4 times, technically I just want to record it as 1 single occurance as a past due. You can see the date change, but I simple want it to be at its LATEST date.
Thoughts, Ideas? I appreciate any advice here.
Hi,
Share sample data of a few PO's in a format that can be pasted in an MS Excel file and show the expected result.
Hey Ashish, Can you let me know if you've got any help or suggestions for me? Thanks for looking into this!
Hi,
I have not understood your requirement. For the table that you have shared, show the expected result.
Thanks for your patience Ashish, Ok so here is a example below:
This purchase order is repeated twice, the only difference is the date column called the "First Promised Date". I would like to just count the latest date a single time, currently this purchase order is being counted twice due to my inaccurate measure. I just want to record it as one single occurance . You can see the date change, but I simple want it to be at its LATEST date visible, i.e. 11/20/2023 MAX not 6/19/2023.
Supplier | PO Number | Item Number | Line | Shipment | Product Line | Unique Shipment | First Promise Date |
KIRSH FOUNDRY INC | 1141140 | S-90988 RAW | 1 | 1 | PMG | 114114011 | 6/19/2023 0:00 |
KIRSH FOUNDRY INC | 1141140 | S-90988 RAW | 1 | 1 | PMG | 114114011 | 11/20/2023 0:00 |
I'll try once again - "For the table that you have shared, show the expected result"
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Supplier", type text}, {"PO#", Int64.Type}, {"Item #", type text}, {"Line", Int64.Type}, {"Shipment", Int64.Type}, {"Product Line", type text}, {"Unique shipment", Int64.Type}, {"First promise date", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Supplier", "PO#", "Item #", "Line", "Shipment", "Product Line", "Unique shipment"}, {{"Count", each Table.Max(_,"First promise date")}}),
#"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"First promise date"}, {"First promise date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"First promise date", type date}})
in
#"Changed Type1"
Hope this helps.
Hey Ashish, So the problem is I cannot access the Power Query Editor since I am pulling this table via DirectQuery. Do you know any measures or custom column that can be used as a workaround?
Hello Ashish - Thank you so much for your help, here is an example ... I appreciate any help you can provide.
Supplier | Count of Buyer | PO Number | Item Number | Line | Shipment | Product Line | Unique Shipment | First Promise Date |
SIGNICAST CORP | 1 | 1127031 | FD-4330 | 1 | 1 | VACUFUSE II | 112703111 | 4/3/2023 0:00 |
SIGNICAST CORP | 1 | 1127036 | FD-4334 | 1 | 1 | VACUFUSE II | 112703611 | 6/2/2023 0:00 |
SIGNICAST CORP | 1 | 1127038 | FD-4329 | 1 | 1 | VACUFUSE II | 112703811 | 6/13/2013 0:00 |
SIGNICAST CORP | 1 | 1129908 | SD-6169 | 1 | 1 | N/A | 112990811 | 6/26/2023 0:00 |
APLICACIONES INDUSTRIALES DE CALIDAD SA DE CV | 1 | 1136111 | F-10264 | 1 | 1 | HOLDERS | 113611111 | 3/9/2023 0:00 |
APLICACIONES INDUSTRIALES DE CALIDAD SA DE CV | 1 | 1136111 | F-10264 | 1 | 1 | HOLDERS | 113611111 | 5/2/2023 0:00 |
ASCHER BROTHERS | 1 | 1136645 | QG-1590233 | 1 | 1 | N/A | 113664511 | 5/5/2023 0:00 |
KIRSH FOUNDRY INC | 1 | 1141140 | S-90988 RAW | 1 | 1 | PMG | 114114011 | 6/19/2023 0:00 |
KIRSH FOUNDRY INC | 1 | 1141140 | S-90988 RAW | 1 | 1 | PMG | 114114011 | 11/20/2023 0:00 |
NAMEPLATE & PANEL TECHNOLOGY | 1 | 1144900 | G-9615 | 2 | 1 | N/A | 114490021 | 8/3/2023 0:00 |
NAMEPLATE & PANEL TECHNOLOGY | 1 | 1144900 | G-9615 | 2 | 1 | N/A | 114490021 | 8/8/2023 0:00 |
NAMEPLATE & PANEL TECHNOLOGY | 1 | 1144900 | G-9615 | 2 | 1 | N/A | 114490021 | 8/15/2023 0:00 |
NAMEPLATE & PANEL TECHNOLOGY | 1 | 1144900 | G-9615 | 2 | 1 | N/A | 114490021 | 8/17/2023 0:00 |
H3 MANUFACTURING GROUP LLC | 1 | 1146571 | CA-240 | 1 | 1 | PMG | 114657111 | 10/31/2022 0:00 |
PRECISION ENTERPRISES INC | 1 | 1146811 | SD-6790 | 1 | 1 | N/A | 114681111 | 7/19/2023 0:00 |
NEP ELECTRONICS INC | 1 | 1147295 | FD-3411 | 3 | 1 | TRIPSAVER II | 114729531 | 11/1/2023 0:00 |
NEP ELECTRONICS INC | 1 | 1147295 | FD-3411 | 3 | 1 | TRIPSAVER II | 114729531 | 12/19/2023 0:00 |
NEP ELECTRONICS INC | 1 | 1147295 | FD-3411 | 3 | 1 | TRIPSAVER II | 114729531 | 1/11/2024 0:00 |
BISON GEAR AND ENGINEERING CO | 1 | 1147416 | SD-5785 | 1 | 4 | PMG | 114741614 | 11/6/2023 0:00 |
BISON GEAR AND ENGINEERING CO | 1 | 1147416 | SD-5785 | 1 | 4 | PMG | 114741614 | 11/14/2023 0:00 |
BISON GEAR AND ENGINEERING CO | 1 | 1147416 | SD-5785 | 1 | 4 | PMG | 114741614 | 1/8/2024 0:00 |
BISON GEAR AND ENGINEERING CO | 1 | 1147416 | SD-5785 | 1 | 5 | PMG | 114741615 | 11/29/2023 0:00 |
BISON GEAR AND ENGINEERING CO | 1 | 1147416 | SD-5785 | 1 | 5 | PMG | 114741615 | 12/11/2023 0:00 |
BISON GEAR AND ENGINEERING CO | 1 | 1147416 | SD-5785 | 1 | 5 | PMG | 114741615 | 1/26/2024 0:00 |
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |