-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
multiple dates
- Thread starter hershys
- Start date
smozgur
BatCoder
- Joined
- Feb 28, 2002
- Messages
- 2,656
Hi, @hershys. Welcome to the MrExcel Message Board.
I am confused because you mentioned
1- You basically have the following data in the worksheet:
And want to extract these two dates in Power Query?
Try following (Table1 is the name of the table)
Result:
2- Or, If I understood the opposite, and you actually have the following:
And want the combine these two columns?
Result:
If either one is wrong, then perhaps you might try providing some sample data and desired result. You can use XL2BB to post sample mini-sheets easily.
I am confused because you mentioned
Text.Combine, so I am not sure what you have and what you are trying to get, and I will try two opposite solutions.1- You basically have the following data in the worksheet:
| Book1 | |||
|---|---|---|---|
| A | |||
| 1 | ShipDate | ||
| 2 | 6/1/2021, 6/3/2021 | ||
| 3 | 5/4/2021, 5/6/2021 | ||
| 4 | 4/3/2021, 4/21/2021 | ||
Sheet1 | |||
And want to extract these two dates in Power Query?
Try following (Table1 is the name of the table)
Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SplitColumn = Table.SplitColumn(Source, "ShipDate", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ShipDate.1", "ShipDate.2"}),
Result = Table.TransformColumnTypes(SplitColumn,{{"ShipDate.1", type date}, {"ShipDate.2", type date}})
in
Result| ShipDate.1 | ShipDate.2 |
| 6/1/2021 | 6/3/2021 |
| 5/4/2021 | 5/6/2021 |
| 4/3/2021 | 4/21/2021 |
2- Or, If I understood the opposite, and you actually have the following:
| Book1 | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | ShipDate.1 | ShipDate.2 | ||
| 2 | 6/1/2021 | 6/3/2021 | ||
| 3 | 5/4/2021 | 5/6/2021 | ||
| 4 | 4/3/2021 | 4/21/2021 | ||
Sheet2 | ||||
And want the combine these two columns?
Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
ChangeType = Table.TransformColumnTypes(Source,{{"ShipDate.1", type date}, {"ShipDate.2", type date}}),
Combined = Table.AddColumn(ChangeType, "Combined", each Text.Combine({Text.From([ShipDate.1]), Text.From([ShipDate.2])}, ","), type text)
in
Combined| ShipDate.1 | ShipDate.2 | Combined |
| 6/1/2021 | 6/3/2021 | 6/1/2021,6/3/2021 |
| 5/4/2021 | 5/6/2021 | 5/4/2021,5/6/2021 |
| 4/3/2021 | 4/21/2021 | 4/3/2021,4/21/2021 |
If either one is wrong, then perhaps you might try providing some sample data and desired result. You can use XL2BB to post sample mini-sheets easily.
Upvote
0
Wow! Thanks for your effort to help me!
Your second example is closer to what I'm doing and I tried using Text.From but results in error. I can only modify M recorded steps. So I'm doing a "group by" step (on order number) and Sum aggregation on Ship Dates, then modifying it from each List.Sum to each Text.Combine. I've done this on other text fields successfully but I need to get the dates as text.
Upvote
0
Here's the step as modified:
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Channel Order #"}, {{"Total", each List.Sum([CustomTotal]), type nullable number}, {"Payments", each List.Sum([Payments]), type number}, {"Refunds", each List.Sum([Payments]), type number}, {"Order Dates", each Text.Combine(Text.From([Order Date]),","), type text}, {"Ship Dates", each Text.Combine(Text.From([Ship Date]),","), type text}, {"SC Orders", each Text.Combine([#"Order #"],","), type text}})
in
Upvote
0
smozgur
BatCoder
- Joined
- Feb 28, 2002
- Messages
- 2,656
That would be really great if you could post sample data (XL2BB is the best way) and use BB code when you post sample code. Then the helpers can work with your data easily and quickly provide solutions.
There might be other solutions, but here is one approach.
This is the data:
The following is the M Code (I used the little M button on the editor toolbar, so it is more readable now):
And the following is the result:
The idea is, changing date field types to date initially, then text (I did that to avoid the hour part - there might be other methods). Then you can use the date fields as text with the
Hope this helps.
There might be other solutions, but here is one approach.
This is the data:
| Book1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Channel Order # | Order # | Payments | CustomTotal | OrderDate | ShipDate | ||
| 2 | 1 | 1_1 | 10 | 1000 | 5/29/2021 | 6/1/2021 | ||
| 3 | 1 | 1_2 | 20 | 2000 | 4/3/2021 | 5/4/2021 | ||
| 4 | 2 | 2_1 | 30 | 3000 | 3/2/2021 | 4/3/2021 | ||
Source | ||||||||
The following is the M Code (I used the little M button on the editor toolbar, so it is more readable now):
Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
ChangeToDate = Table.TransformColumnTypes(Source, {{"OrderDate", type date}, {"ShipDate", type date}}),
ChangeToText = Table.TransformColumnTypes(ChangeToDate,{{"OrderDate", type text}, {"ShipDate", type text}}),
GroupRows = Table.Group(ChangeToText,
{"Channel Order #"},
{
{"Total", each List.Sum([CustomTotal]), type nullable number},
{"Payments", each List.Sum([Payments]), type nullable number},
{"Refunds", each List.Sum([Payments]), type nullable number},
{"SC Orders", each Text.Combine([#"Order #"],","), type text},
{"Order Dates", each Text.Combine([OrderDate] , ", "), type text},
{"Ship Dates", each Text.Combine([ShipDate] , ", "), type text}
})
in
GroupRows| Channel Order # | Total | Payments | Refunds | SC Orders | Order Dates | Ship Dates |
| 1 | 3000 | 30 | 30 | 1_1,1_2 | 5/29/2021, 4/3/2021 | 6/1/2021, 5/4/2021 |
| 2 | 3000 | 30 | 30 | 2_1 | 3/2/2021 | 4/3/2021 |
The idea is, changing date field types to date initially, then text (I did that to avoid the hour part - there might be other methods). Then you can use the date fields as text with the
Text.Combine function.Hope this helps.
Upvote
0
Solution
Yes! This did it! Thanks
Upvote
0
Similar threads
