Power Query Question: Extracting Dates from Grouped Lists

Slicemahn

Board Regular
Joined
Jun 10, 2004
Messages
120
Hi Mr. Excel Nation!

I have been trying to extract a single date (Min) or the earliest date and the latest date (Max) from a grouped list using Power Query.
I have only posted a snip of the data and taken out confidential columns. But the original dataset is 15K rows of product materials along with their availability on the market dates.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Matl No", type text}, {"Matl Desc", type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Matl No", type text}, {"Matl Desc", type text}, {"Market Date", type datetime}, {"DeMarket Date", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Matl Desc", "Matl No"}, {{"All Data", each _, type table [Matl No=nullable number, Matl Desc=nullable text, Market Date=nullable datetime, DeMarket Date=nullable datetime]}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Matl No", "Matl Desc", "All Data"}),
    #"Add to List" = Table.AddColumn(#"Reordered Columns","Market Date", each [All Data][Market Date]),
    #"Addition to List" = Table.AddColumn(#"Add to List","DeMarket Date", each [All Data][DeMarket Date]),
    #"Extract From List" = Table.TransformColumns( #"Addition to List", {"Market Date", each Text.Combine(List.Min(List.Transform(_, Text.From)), "#(lf)"), type text})
in
    #"Extract From List"


Any help you can provide will be appreciated!

Thank you for your help!
 

Attachments

  • Screenshot 1.jpg
    Screenshot 1.jpg
    39.5 KB · Views: 14
  • Screenshot 2.jpg
    Screenshot 2.jpg
    79.1 KB · Views: 11
  • Screenshot 3.jpg
    Screenshot 3.jpg
    248.6 KB · Views: 12

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
I would simply use the aggregates min/max during grouping.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top